CODEWITHSUNDEEP
mysqlleft-join
Faisal Shahzad
Faisal Shahzad

Reputation: 579

Mysql left join: show rows based on another attribute from the joined table

I have following tables:

   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

When a user is logged in, he should view the list of those pools for which he is not a member e.g If user with id 101 is logged in, following result should be shown.

Pool              Members
xyz                2 members

Upvotes: 1

Views: 37

Answers (1)

Sadikhasan
Sadikhasan

Reputation: 18600

Might be you want to this

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

Upvotes: 2

Related Questions