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:
id | name
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?
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;
SELECT * FROM groups g INNER JOIN memberships m ON m.group_id = g.id WHERE m.user_id = '1' OR m.user_id = '2'