Joins Based On Conditions In Multiple Tables

Three tables, columns as follows: A: A_id, B_id, C_id, flag, ... B: B_id, date, ... C: C_id, date If A.flag is NULL, then I need all rows from A joined with B on B_id that h

Solution 1:

You could try

SELECT a.*, b.* 
FROM a INNER JOIN b ON a.B_id = b.B_id
WHERE a.flag IS NULL AND b.date < NOW()
UNION
SELECT a.*, b.* 
FROM a INNER JOIN b ON a.B_id = b.B_id
INNER JOIN c ON a.C_id = c.C_id
WHERE a.flag IS NOT NULL AND c.date < NOW()

Solution 2:

this will probably do the trick for you!

SELECT a.*
FROM A AS a
LEFT JOIN B AS b ON b.b_id = a.b_id AND NOW() > b.date AND a.flag IS NULL
LEFT JOIN C AS c ON c.c_id = a.c_id AND NOW() > c.date AND a.flag IS NOT NULL

If you only want rows from A that matches either of these criteria, you need to add a where like this:

WHERE b.b_id IS NOT NULL OR c.c_id IS NOT NULL

Otherwise you will end up with all rows in A. :)

The complete query would be this:

SELECT a.*
FROM A AS a
LEFT JOIN B AS b ON b.b_id = a.b_id AND NOW() > b.date AND a.flag IS NULL
LEFT JOIN C AS c ON c.c_id = a.c_id AND NOW() > c.date AND a.flag IS NOT NULL
WHERE b.b_id IS NOT NULL OR c.c_id IS NOT NULL

Solution 3:

Something like this might work as well:

SELECT *
FROM A

WHERE A.flag IS NULL
  AND EXISTS (SELECT * FROM B WHERE date < NOW() AND B_id = A.A_id)

   OR A.flag IS NOT NULL
  AND EXISTS (SELECT * FROM C WHERE date < NOW() AND C_id = A.A_id)