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.