SQL problem - select accross multiple tables (user groups)

Question

I have a db schema which looks something like this:

create table user         (id int, name varchar(32));
create table group        (id int, name varchar(32));
create table group_member (group_id int, user_id int, flag int);

I want to write a query that allows me to so the following:

Given a valid user id (UID), fetch the ids of all users that are in the same group as the specified user id (UID) AND have group_member.flag=3.

Rather than just have the SQL. I want to learn how to think like a Db programmer. As a coder, SQL is my weakest link (since I am far more comfortable with imperative languages than declarative ones) - but I want to change that.

Anyway here are the steps I have identified as necessary to break down the task. I would be grateful if some SQL guru can demonstrate the simple SQL statements - i.e. atomic SQL statements, one for each of the identified subtasks below, and then finally, how I can combine those statements to make the ONE statement that implements the required functionality.

Here goes (assume specified user_id [UID] = 1):

//Subtask #1.
Fetch list of all groups of which I am a member

Select group.id from user inner join group_member where user.id=group_member.user_id and user.id=1

//Subtask #2
Fetch a list of all members who are members of the groups I am a member of (i.e. groups in subtask #1)

Not sure about this ...

select user.id from user, group_member gm1,  group_member gm2, ... [Stuck]

//Subtask #3
Get list of users that satisfy criteria group_member.flag=3
Select user.id from user inner join group_member where user.id=group_member.user_id and user.id=1 and group_member.flag=3

Once I have the SQL for subtask2, I'd then like to see how the complete SQL statement is built from these subtasks (you dont have to use the SQL in the subtask, it just a way of explaining the steps involved - also, my SQL may be incorrect/inefficient, if so, please feel free to correct it, and point out what was wrong with it).

Thanks

Answer 1

The beauty of SQL is that you can join these three selects together with one efficient join. In this case I used the WHERE version of a join because I find it easier to understand. But you might also look at the syntax for LEFT JOIN and INNER JOIN because they give you a lot of expressivity.

SELECT * FROM user, group_member, group 
WHERE user.id = group_member.user_id
&& group_member.group_id = group.id
&& group_member.flag = 3

Answer 2

This should find all people in the same group as a certain user, with the specified flag.

SELECT DISTINCT g2.user_id
FROM group_member AS g INNER JOIN group_member AS g2
  ON g.group_id = g2.group_id
WHERE g.user_id = <the userid you want to find>
  AND g2.flag = 3

To approach the problem, I did the following:

We need to compare two group_members, as we want to know which ones are in the same group, so we'll need to join group_member with itself. (On group_id, as we want the ones in the same group.)

I want to make sure that one of them is the user I want to compare it to, and then the other one must have the correct flag.

Once this is done, I simply need to pull out the user_id from the one I compare to my original user. Since I figure a user might be in two groups another user is in, it might also be wise to add a DISTINCT to ensure that we only get each user_id out of it once.

Answer 3

Query 1 - Select all groups I am a member of.

You don't need a join here unless you also want the groups' names. Just check the group_member table.

SELECT group_id
FROM group_member
WHERE user_id = 1

Result:

1
3

Query 2: Select all users in one of the same groups as me.

You can self-join the group_member table to find all the users that are in the same group as each other and then add a where clause to only find all those that are in the same group as yourself. Add DISTINCT to make sure you don't get people twice.

SELECT DISTINCT T2.user_id
FROM group_member AS T1
JOIN group_member AS T2
ON T1.group_id = T2.group_id
WHERE T1.user_id = 1
AND T2.user_id <> 1 -- Remove myself

Result:

2
3
5

Query 3: Users who have flag 3 in any group.

You just need to check the group_member table. Again, add DISTINCT if you only want to see each user once.

SELECT DISTINCT user_id
FROM group_member
WHERE group_member.flag=3

Result:

2
3
4

Final query: Users in the same group as me who have flag 3.

This is almost the same as query two, just add an extra WHERE condition.

SELECT DISTINCT T2.user_id
FROM group_member AS T1
JOIN group_member AS T2
ON T1.group_id = T2.group_id
WHERE T1.user_id = 1
AND T2.user_id <> 1 -- Remove myself
AND T2.flag = 3

Result:

2
3

Test data:

create table user         (id int, name varchar(32));
create table `group`        (id int, name varchar(32));
create table group_member (group_id int, user_id int, flag int);

insert into user (id, name) VALUES (1, 'user1'), (2, 'user2'), (3, 'user3'), (4, 'user4'), (5, 'user5');
insert into `group` (id, name) VALUES (1, 'group1'),(2, 'group2'), (3, 'group3');
insert into group_member (group_id, user_id, flag) VALUES (1, 1, 0), (1, 2, 3), (1, 3, 3), (2, 3, 3), (2, 4, 3), (2, 5, 0), (3, 1, 0), (3, 5, 0);