多对多关联中根据table_2的两个ID查询table_1数据的SQL问题

原SQL的问题 #

1. 关联字段错误：JOIN table_2时，ON子句写成了table_2.id=table_1_table_2.table_2，中间表的关联字段应为table_2_id，正确条件是table_2.id = table_1_table_2.table_2_id。
2. WHERE条件逻辑矛盾：table_2.id = 1 AND table_2.id = 2不可能成立，单条记录的id无法同时等于两个不同值，这直接导致查询无结果。

两种可行的正确写法 #

方法一：GROUP BY + HAVING（适合匹配多个table_2_id的场景） #

先筛选关联了目标table_2_id的记录，按table_1分组后，筛选出包含所有目标table_2_id的分组：

SELECT t1.*
FROM table_1 t1
JOIN table_1_table_2 t1t2 ON t1.id = t1t2.table_1_id
WHERE t1t2.table_2_id IN (1, 2)
GROUP BY t1.id, t1.name
HAVING COUNT(DISTINCT t1t2.table_2_id) = 2;

• 用IN (1,2)筛选关联了任意目标id的记录
• GROUP BY按table_1的字段分组
• HAVING COUNT(DISTINCT ...) = 2确保分组内同时包含两个不同的table_2_id

方法二：多次JOIN中间表（适合匹配少量table_2_id的场景） #

通过两次关联中间表，分别匹配table_2_id=1和2，确保table_1同时存在这两个关联：

SELECT t1.*
FROM table_1 t1
JOIN table_1_table_2 t1t2_1 ON t1.id = t1t2_1.table_1_id AND t1t2_1.table_2_id = 1
JOIN table_1_table_2 t1t2_2 ON t1.id = t1t2_2.table_1_id AND t1t2_2.table_2_id = 2;

• 每次JOIN都指定匹配一个table_2_id
• 只有同时满足两次JOIN条件的table_1记录才会被保留

内容的提问来源于stack exchange，提问作者Yurei