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'
