True LEFT JOIN for non-existing relationships

SQL++
1

Hi,

For simplicity’s sake, lets say I have the following DB:

{
    id: "a_id",   // This is the key, not a field in the document
    type: "a"
}
{
    id: "b_id",
    type: "b",
    a: "a_id"
}
{
     type: "c",
     b: "b_id"
}

As you can see, there is a classic parent-child relationship (“a” HAS MANY “b”, “b” HAS MANY “c”)
Lets assume that there will not always be a type “c” document - for example, I have five “b” documents, but only one has a “c” document related to it.
I want to write a JOIN query that will give me all “b” that belong to “a”, and if they exist - all “c” that belong to “b”, but for “b” documents that don’t have “c” I don’t get any results - I would like to get all five “b” documents, as well as the one “c” document that is related.
My query looks like this:

SELECT *
FROM default AS a
JOIN default AS b ON KEY b.a FOR a
JOIN default AS c ON KEY c.b FOR b
WHERE a.type = "a"
AND b.type = "b"
AND c.type = "c";

What am I missing here?
Thanks!

2

WHERE clause post join filter if if c is MISSING c.type = “c” is false.

Try this.

SELECT *
FROM default AS a
LEFT JOIN default AS b ON KEY b.a FOR a
LEFT JOIN default AS c ON KEY c.b FOR b
WHERE a.type = "a"
AND b.type = "b"
AND (c IS MISSING OR c.type = "c");

Also checkout

3

Didn’t work for me… still only brings “b” that has “c”…

4

Hi @Spyes,

The following seems work fine. it returns 5 rows.

INSERT INTO default VALUES("a_id", { "type": "a" });
INSERT INTO default VALUES("b_id", { "type": "b", "a":"a_id"});
INSERT INTO default VALUES("b_id1", { "type": "b", "a":"a_id"});
INSERT INTO default VALUES("b_id1", { "type": "b", "a":"a_id"});
INSERT INTO default VALUES("b_id2", { "type": "b", "a":"a_id"});
INSERT INTO default VALUES("b_id3", { "type": "b", "a":"a_id"});
INSERT INTO default VALUES("b_id4", { "type": "b", "a":"a_id"});
INSERT INTO default VALUES("c_id", { "type": "c", "b":"b_id"});

CREATE INDEX ia ON default(a);
CREATE INDEX ib ON default(b);

SELECT *
FROM default AS d
LEFT JOIN default AS d1 ON KEY d1.a FOR d
LEFT JOIN default AS d2 ON KEY d2.b FOR d1
WHERE d.type = "a"
AND d1.type = "b"
AND (d2 IS MISSING OR d2.type = "c");
1 Like
5

Yup, that worked, thanks! I had missed part of your earlier query, but now looking at it again I see my mistake.
Thank you!