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_idis null (B is absent)
b_idis not null (B is present), AND its associated C fits a particular criterion (
C.activeis 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.