Cordello Cordello - 18 days ago 7
SQL Question

Multiple people in multiple groups SQL

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;


To list all the groups a person belongs to, I do this query:

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;


I have to use the distinct command, otherwise it will print the list a number of times equal to the total number of groups. So if I have 5 groups and want a list of people in a particular group, it will print the same list 5 times. I don't know why it would do that. But I threw the word DISTINCT in and then it worked.

So it's functional, but it's sloppy, and I don't know if that's the way it's usually done. How do people usually do it?

Answer

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 groups table.

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.