基于多个表中的条件联接 [英] Joins based on conditions in multiple tables

问题描述

三个表，列如下:

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

如果 A.flag 为 NULL，那么我需要来自 A 的所有行与 B_id 上的 B 连接，这些行过去具有 B.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.

如果 A.flag 不是 NULL，那么我需要所有来自 A 的行与 B_id 上的 B 连接，这些行过去具有 C.date，C 连接在 C_id 上.

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.

尝试:

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

但我需要在 A.flag is NULL 行中设置一些条件来阻止它与 C 中的每一行连接.这是我无法解决的.

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?

推荐答案

你可以试试

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

这篇关于基于多个表中的条件联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！