这是一个案例关系划分。我们在这个相关问题下收集了一系列技术:
- 如何在多通关系中过滤 SQL 结果 https://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation/7774879
特殊的困难是排除额外的用户。基本上有4种技术。
- 选择其他表中不存在的行 https://stackoverflow.com/questions/19363481/select-rows-which-are-not-present-in-other-table/19364694#19364694
我建议LEFT JOIN
/ IS NULL
:
SELECT cu1.conversation_id
FROM conversation_user cu1
JOIN conversation_user cu2 USING (conversation_id)
LEFT JOIN conversation_user cu3 ON cu3.conversation_id = cu1.conversation_id
AND cu3.user_id NOT IN (3,32)
WHERE cu1.user_id = 32
AND cu2.user_id = 3
AND cu3.conversation_id IS NULL;
Or NOT EXISTS
:
SELECT cu1.conversation_id
FROM conversation_user cu1
JOIN conversation_user cu2 USING (conversation_id)
WHERE cu1.user_id = 32
AND cu2.user_id = 3
AND NOT EXISTS (
SELECT 1
FROM conversation_user cu3
WHERE cu3.conversation_id = cu1.conversation_id
AND cu3.user_id NOT IN (3,32)
);
两个查询都做not取决于一个UNIQUE
约束为(conversation_id, user_id)
,可能存在也可能不存在。意思是,查询甚至可以工作,如果user_id
32(或 3)在同一对话中多次列出。你would但是,结果中出现重复行,并且需要应用DISTINCT
or GROUP BY
.
唯一的条件是您制定的条件:
...显示只有用户 32 和用户 3 之间存在对话的查询?
审核查询
The 您在评论中链接的查询 http://pastebin.com/tNVifwmu行不通的。您忘记排除其他参与者。应该是这样的:
SELECT * -- or whatever you want to return
FROM conversation_user cu1
WHERE cu1.user_id = 32
AND EXISTS (
SELECT 1
FROM conversation_user cu2
WHERE cu2.conversation_id = cu1.conversation_id
AND cu2.user_id = 3
)
AND NOT EXISTS (
SELECT 1
FROM conversation_user cu3
WHERE cu3.conversation_id = cu1.conversation_id
AND cu3.user_id NOT IN (3,32)
);
这与其他两个查询类似,只是它不会返回多行,如果user_id = 3
已链接多次。