Say I have a table C that references rows from tables A and B:
id, a_id, b_id, ...
and a simple query:
SELECT * FROM C WHERE a_id=X AND b_id=Y
I would like to differentiate between the following cases:
- No row exists in A where id = X
- No row exists in B where id = Y
- Both such rows in A and B exist, but no rows in C exist where a_id = X and b_id = Y
The above query will return empty result in all those cases.
In case of one parent table I could do a
LEFT JOIN like:
SELECT * FROM A LEFT JOIN C ON a.id = c.a_id WHERE c.a_id = X
and then check if the result is empty (no row in A exists), has one row with NULL
c.id (row in A exists, but no rows in C exist) or 1+ rows with non-NULL
c.id (row in A exists and at least one row in C exists). A bit messy but it works, but I was wondering if there is a better way of doing this, especially if there is more than one parent table?