SQL problem - select accross multiple tables (user groups)
- by morpheous
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 (foobar_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
