Join one table with two other ones by id

I am trying to join one table with two others that are unrelated to each other but are linked to the first one by an id

I have the following tables

create table groups(
 id int,
 name text
);

create table members(
 id int,
 groupid int,
 name text
);

create table invites(
 id int,
 groupid int,
 status int    \ 2 for accepted, 1 if it's pending
);

Then I inserted the following data

insert into groups (id, name) values(1,'group');
insert into members(id, groupid, name) values(1,1,'admin'),(1,1,'other');
insert into invites(id, groupid, status) values(1,1,2),(2,1,1),(3,1,1);

Obs:

• The admin does not has an invite
• The group has an approved invitation with status 2 (because the member ‘other’ joined)
• The group has two pending invites with status 1

I am trying to do a query that gets the following result

groupid | name  | inviteId
1       | admin | null
1       | other | null
1       | null  | 2
1       | null  | 3        

I have tried the following querys with no luck

select g.id, m.name, i.id from groups g
left join members m ON m.groupid = g.id
left join invites i ON i.groupid = g.id and i.status = 1;

select g.id, m.name, i.id from groups g
join (select groupid, name from members) m ON m.groupid = g.id
join (select groupid, id from invites where status = 1) i ON i.groupid = g.id;

Any ideas of what I am doing wrong?

Answer

Because members and invites are not related, you need to use two separate queries and use UNION (automatically removes duplicates) or UNION ALL (keeps duplicates) to get the output you desire:

select g.id as groupid, m.name, null as inviteid from groups g
  join members m ON m.groupid = g.id
union all 
select g.id, null, i.id from groups g
  join invites i ON (i.groupid = g.id and i.status = 1);

Output:

 groupid | name  | inviteid 
---------+-------+----------
       1 | admin |         
       1 | other |         
       1 |       |        3
       1 |       |        2
(4 rows)

Without a UNION, your query implies that the tables have some sort of relationship, so the columns are joined side-by-side. Since you want to preserve the null values, implying that the tables are not related, you need to concatenate/join them vertically with UNION

Disclosure: I work for EnterpriseDB (EDB)