Unify these 2 queries that use 3 tables

Question

I am working in a group and group membership functionality

Tables relevant are:

usuarios (id, first_name, last_name, ...)

groups (id, name, .. , id_user )

group_members (id, id_group, id_user)

With this Design i need to get the creator from the groups table (groups.id_user is the creator) and the members from the group_members (group_members.id_usuario)

Wich for now I have only been able to achieve with 2 querys:

/* Retrieve members */
$q = 'SELECT usuarios.id as uid, usuarios.avatar, usuarios.first_name,usuarios.last_name 
                  FROM usuarios LEFT JOIN group_members ON usuarios.id = group_members.id_user
                  WHERE group_members.id_group = '.$this->id.'';
$r =  cache_query($q,'',10);
/* Retrive creator */           
$q2 = 'SELECT usuarios.id as uid, usuarios.avatar, usuarios.first_name,usuarios.last_name
                   FROM usuarios LEFT JOIN groups ON usuarios.id = groups.id_user
                   WHERE groups.id = '.$this->id;
$r2 =  cache_query($q2,'',10);

Is there a way to achieve it with one single query? (i was thinking also just to insert the creator as a member and only use first query)

D'Arcy Rittich · Answer 1 · 2012-07-24T13:56:05.947

3

If you join the usuarios table twice, then you can get both member and group creator info at the same time:

select u.id as uid,
    u.avatar,
    u.first_name,
    u.last_name,
    uc.id as creator_uid,
    uc.avatar as creator_avatar,
    uc.first_name as creator_first_name,
    uc.last_name as creator_last_name
from groups g
inner join group_members gm on g.id = gm.id_group
left outer join usuarios u on gm.id_user = u.id
left outer join usuarios uc on g.id_user = uc.id
where g.id = ...

edited Jul 24 '12 at 13:56

answered Jul 24 '12 at 13:44

D'Arcy Rittich

167,292
40
290
283

Sorry, I didn't realise that you where renaming the creator columns, I hope that is not necessary; Is there a way to have the creator as an extra row? – Toni Michel Caubet Jul 24 '12 at 13:58
@ToniMichelCaubet Sure, just use your two queries with a UNION ALL between them. But my approach works for more than one group, as well. – D'Arcy Rittich Jul 24 '12 at 14:14

score 0 · Answer 2 · answered Jul 24 '12 at 13:45

0

You need to combine both queries and add OR condition in WHERE clause:

SELECT usuarios.id as uid, usuarios.avatar, usuarios.first_name,usuarios.last_name
FROM usuarios
     LEFT JOIN group_members
        ON usuarios.id = group_members.id_user
     LEFT JOIN groups
        ON usuarios.id = groups.id_user
WHERE (group_members.id_group =  '.$this->id.' OR groups.id = '.$this->id)

answered Jul 24 '12 at 13:45

Omesh

27,801
6
42
51

ya SELECT DISTINCT will work as you have duplicates in one of the tables used for joins. – Omesh Jul 25 '12 at 07:54

Unify these 2 queries that use 3 tables

2 Answers2