0

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.

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);
  • This works perfectly, thank you! I see that you're using outer joins and then constraining the results via the where clause (and will think about this some more). – SexxLuthor Jan 22 at 10:56
  • 1
    I'm glad to help. – McNets Jan 22 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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