mysql使用if语句连接三个表

Question

我有一个有效的查询

SELECT *
FROM the_index AS t1
LEFT JOIN table_2 AS t2 ON (t1.activity_id = t2.id) 
LEFT JOIN table_3 AS t3 ON (t1.activity_id = t3.id)
ORDER BY t1.id;

这很好用，但我想用一些if语句来修改它。

SELECT *
FROM the_index AS t1
LEFT JOIN table_2 AS t2 ON (t1.activity_id = t2.id)     <---- If t1.event = '2'
LEFT JOIN table_3 AS t3 ON (t1.activity_id = t3.id)     <---- If t1.event = '3'
ORDER BY t1.id;

Answer 1

只需使用以下命令：

LEFT JOIN table_2 AS t2 ON (t1.activity_id = t2.id AND t1.event = '2')
LEFT JOIN table_3 AS t3 ON (t1.activity_id = t3.id AND t1.event = '3')

请注意，这只在使用LEFT JOIN时有效。不适用于内连接。

Answer 2

你的意思是

SELECT *
FROM the_index AS t1
LEFT JOIN table_2 AS t2 ON (t1.activity_id = t2.id AND t1.event = '2')
LEFT JOIN table_3 AS t3 ON (t1.activity_id = t3.id AND t1.event = '3')
ORDER BY t1.id;

警告:这显然违背了MySQL关于不要将WHERE-like子句放入连接的建议。