CODEWITHSUNDEEP
mysqlsqljoin
jontyc
jontyc

Reputation: 3495

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 have B.date in the past.

If A.flag is not NULL, then I need all rows from A joined with B on B_id that have C.date in the past, C being joined on C_id.

An 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 A.flag is NULLline to stop it joining with each row from C. That is what I can't work out.

Or is there an easier way to do this?

Upvotes: 1

Views: 324

Answers (3)

Andriy M
Andriy M

Reputation: 77687

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)

Upvotes: 0

mikn
mikn

Reputation: 484

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

Upvotes: 0

Marco
Marco

Reputation: 57583

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

Upvotes: 3

Related Questions