Condition Based Compounds in Multiple Tables

Question

Three tables, columns:

A: A_id, B_id, C_id, flag, ... B: B_id, date, ... C: C_id, date 

If A.flag is NULL, I need all the lines from A connected to B by B_id that B.date had in the past.

If A.flag is not NULL, then I need all the lines from A connected to B on B_id that have Cdate in the past, C joins on C_id.

Attempt:

 SELECT * FROM A, B, C WHERE A.A_id = B.B_id AND ((A.flag IS NULL AND (NOW() > B.date) OR (A.flag IS NOT NULL AND (NOW() > C.date) AND C.C_id = A.C_id)) 

But I need some condition in the line A.flag is NULL to stop it connecting to every line from C. This is something that I cannot solve.

Or is there an easier way to do this?

Answer 1

You can 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() 

Answer 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 need lines from A that match any of these criteria, you need to add the following:

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

Otherwise, you will get all rows in A. :)

The full request will be as follows:

 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 

Answer 3

Something like this might work:

 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)