Mysql左连接: show rows based on another attribute from the joined table

Question

我有下表:

   Pools                         Members                             User
----------------------------------------------------------------------------------
id      name              id     pool_id     user_id              id      name
----------------------------------------------------------------------------------
1       abc               1         1          101                101     test1
2       xyz               2         1          102                102     test2
                          3         2          105                105     test5
                          4         2          106                106     test6

当用户登录时，他应该查看他不是其成员的那些池的列表，例如，如果 ID 为 101 的用户登录，则应显示以下结果。

Pool              Members
xyz                2 members

Answer 1

可能是你想要的

SELECT name AS Pool,
       count(pool_id) AS Members
FROM Pools
JOIN Members ON Pools.id=Members.pool_id
WHERE Pool.id NOT IN
    (SELECT pool_id
     FROM Members
     WHERE user_id=101)
GROUP BY Member.pool_id