按id将一张表与另外两张表连接起来

Question

我正试图将一个表与另外两个表连接起来，这两个表彼此无关，但通过id 链接到第一个表

我有以下表格

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
);

然后我插入了以下数据

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:

• 管理员没有邀请
• 该群有一个状态为2的已批准邀请(因为成员"other"已加入(
• 该组有两个状态为1的挂起邀请

我正在尝试进行一个查询，得到以下结果

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

我尝试了以下查询，但没有运气

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;

你知道我做错了什么吗？

Answer 1

因为members和invites不相关，您需要使用两个单独的查询，并使用UNION(自动删除重复项(或UNION ALL(保留重复项(来获得您想要的输出：

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);

输出：

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

如果没有UNION，则查询意味着表具有某种关系，因此列是并排连接的。由于您希望保留null值，这意味着表不相关，因此需要使用UNION将它们垂直连接/联接

披露：我在EnterpriseDB(EDB(工作