September 10, 2013

MySQL Select columns which has two matches in other table

Adura’s Question:

I am sorry if this question was already solved, but I did not know how to word my problem properly or what I should search for.

So I have 2 tables:

groups

id | name

memberships

id | user_id | group_id

What I am trying to do is find all groups which the user with id 1 is a member of and also user with id 2 is a member of.
Obviously that does not work:

SELECT groups.id FROM groups, memberships WHERE groups.id = memberships.group_id AND memberships.user_id = 1 AND memberships.user_id = 2;

I hope you understand my issue, I am having trouble finding the right words for the problem. Feel free to ask.

Edit: Both users should be a member of the group.

If I understood well, you need groups where both users are members?

Something like:

SELECT g1.id 
FROM groups g1, memberships m1, groups g2, memberships m2
WHERE m1.group_id = g1.id AND m1.user_id = 1
AND m2.group_id = g2.id AND m2.user_id = 2
AND g1.group_id = g2.group_id;

Try this:

SELECT * 
FROM   groups g 
       INNER JOIN memberships m 
               ON m.group_id = g.id 
WHERE  m.user_id = '1' 
        OR m.user_id = '2' 

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!