If then join else other join

Question

I am trying to figure out a query which joins several tables (cca 8). At one point I need to join one of two tables. So lets call the result until this point A. Now I want to join B, or C. When I use B, I get smaller result, or no result. If there is no result after the join, I need to join C instead.

To summarize, intersection of (A,C) gives bigger result and I only want to join C if intersection of (A,B) is empty.

What would be a smooth way to say this in mysql?

Answer 1

Only join if the first join primary key is null:

SELECT *
FROM A
LEFT JOIN B ON A.id = B.id
LEFT JOIN C ON B.id IS NULL AND A.id = C.id