Joining three tables with intermediate nullable option and filter condition

Question

Sorry if this seems like a dumb question, but I've read through tens of answers and tried many different options, yet my query results were either overly inclusive or exclusive.

The basic scenario is, having tables A <- B <- C, and the foreign keys A.b_id (nullable) and B.c_id, how can I get A's where either:

• b_id is null (B is absent)
• b_id is not null (B is present), AND its associated C fits a particular criterion (C.active is true, or similar)

To restate, an A may be independent of a B, but every B belongs to a C, and I would like to get the A's that either have no B or have a B that belongs to a C with a specific condition.

Many answers seem to deal with an A that is joined to both B and C, but in this case it's the middle or intermediate table that is optional. (And I haven't been able to figure out how to assemble parts of different examples into a working solution.)

It may be possible through a UNION, but that also seems like a last resort. Is there a way to do this through joins?

I had initially tried two outer joins, but of course that's not the same thing.

I later tried inner joining B and C and then having A left join that as a derived (aliased) table, but this would still return all results, even where (using the example) active was false.

In that case it's like everything is still being outer-joined, but I thought the derived table strategy (seen in different posts) was supposed to solve that problem.

Thanks.

Answer 1

Theoretically this should work according your conditions:

SELECT
    *
FROM
    A
LEFT JOIN
    B
    ON B.id = A.b_id
LEFT JOIN
    C
    ON C.b_id = B.id
WHERE
    (B.id IS NULL) OR (C.active = 1);

Answer 2

The proposed approach did not work for me. It seemed to eliminate entire rows instead of leaving nulls in the output.

I ended up with a single Left Join by combining the B and C tables into a combined "table" via a SELECT statement. In my case A -> B could have gaps, but B-> C would never have gaps.

SELECT *
FROM
A
LEFT JOIN 
  (SELECT innerC.showMe, innerB.id, ... from C innerC, B innerB 
   where innerC.active = 1 and innerC.b_id = innerB.id) combined
ON combined.id = A.b_id
-- combined.id maps to innerB.id in the select.