I have a table for people and a table for the possible groups they can join. Multiple people can be in the same group, obviously, and one person can be in multiple groups. I'm having trouble figuring out how to do this with MySQL, or any SQL database for that matter. I have a solution, but I don't know if that's what is typically done.
What I tried was having a third table, which I called memberships that consisted of a column for a person_id and a group_id. So really, it's a table for ordered pairs.
So I have people with a name and a primary key, groups with a name and a primary key, and then memberships with a person's primary key and a group's primary key. That way, I can just list the same person's primary key multiple times against different to make them part of multiple groups.
Then to list all the members of a particular group, I would do this query:
SELECT DISTINCT users.name AS 'Members of Group 2'
FROM users, memberships, groups
WHERE users.user_id = memberships.user_id
AND memberships.group_id = 2;
SELECT DISTINCT groups.name AS 'Groups of User 3'
FROM groups, memberships, users
WHERE groups.group_id = memberships.group_id
AND memberships.user_id = 3;
Never use commas in the
FROM clause. Always use explicit
JOIN syntax. Your query should be:
SELECT u.name as Group2Member FROM users u JOIN memberships m ON u.user_id = m.user_id WHERE m.group_id = 2;
Note: You do not need the
For the groups a member is in:
SELECT g.name as User3Group FROM memberships m join groups g ON g.group_id = m.group_id WHERE m.user_id = 3;
You should not use and do not need
DISTINCT for either query. That is just a waste of computational resources.