mysql - 使用mySQL检查2个用户是否在同一个房间

Question

我有一个 user_to_room 表

roomID | userID 

我现在想检查是否有 2 个用户在同一个房间

SELECT roomID
FROM   room_to_user
WHERE  userID = 1 AND 2

如何通过 JOIN 实现这一点？

Answer 1

做一个自连接:

SELECT DISTINCT u1.roomID AS roomID
FROM   user_to_room u1, user_to_room u2
WHERE  u1.userID = 1 AND u2.userID = 2
AND    u1.roomID = u2.roomID;

参见 demo .