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.



2 Answers 2


Theoretically this should work according your conditions:

    ON B.id = A.b_id
    ON C.b_id = B.id
    (B.id IS NULL) OR (C.active = 1);

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 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.