我想要的是用户的每一个可能的组合。id和Groups。id与GroupMembership。当GroupMembership表中没有条目时,id为null;
比如说有3个组,3个用户。用户1在组1中,用户2在组1 &2、用户3没有组,组3没有用户。我正在寻找一个清单,看起来有点像这样:
--> user1
U.id = 1
G.id = 1
Gm.id = 1
U.id = 1
G.id = 2
Gm.id = (null - I.e there isn't an entry in the table that intersects this U.id & G.id combo)
U.id = 1
G.id = 3
Gm.id = (null - I.e there isn't an entry in the table that intersects this U.id & G.id combo)
--> user2
U.id = 2
G.id = 1
Gm.id = 2
U.id = 2
G.id = 2
Gm.id = 3
U.id = 2
G.id = 3
Gm.id = (null - I.e there isn't an entry in the table that intersects this U.id & G.id combo)
--> user3
U.id = 3
G.id = 1
Gm.id = (null - I.e there isn't an entry in the table that intersects this U.id & G.id combo)
U.id = 3
G.id = 2
Gm.id = (null - I.e there isn't an entry in the table that intersects this U.id & G.id combo)
U.id = 3
G.id = 3
Gm.id = (null - I.e there isn't an entry in the table that intersects this U.id & G.id combo)
我可以像这样获得所有用户和可能的gms:
SELECT t1.id, t2.idGroupMembership
FROM auth.users t1
LEFT JOIN AcmeCo.GroupMembership t2 ON t1.id = t2.idUser
WHERE t1.staff = 1
ORDER BY id, idGroup
但是,如果我尝试像这样将Groups插入到等式中…:
SELECT t1.email, t2.idGroupMembership, t3.idGroup
FROM auth.users t1
LEFT JOIN AcmeCo.GroupMembership t2 ON t1.id = t2.idUser
LEFT JOIN AcmeCo.Groups t3 ON t3.idGroup = t2.idGroup
WHERE t1.staff = 1
…我没有得到完整的组集,它没有显示不相交的记录。希望你能理解。
谢谢
您可以使用CROSS JOIN。这对我工作使用sqlite和您的测试数据。
SELECT U.id, G.id, Gm.id
FROM Users U CROSS JOIN Groups G
LEFT JOIN GroupMembership Gm
ON U.id = Gm.idUser AND G.id = Gm.idGroup;
结果: 1|1|1
1|2|
1|3|
2|1|2
2|2|3
2|3|
3|1|
3|2|
3|3|
要获得两个表之间的所有可能的组合,您不应该LEFT JOIN它们。下面的查询将达到这个目的:
SELECT t1.email, t2.idGroup
FROM auth.users AS t1, AcmeCo.Groups t2
现在,要选择组成员ID,这样做:
SELECT t1.email, t2.idGroup,
(SELECT idGroupMembership FROM AcmeCo.GroupMembership WHERE idUser=t1.id AND idGroup=t2.idGroup) AS idGroupMembership
FROM auth.users AS t1, AcmeCo.Groups t2
ORDER BY t1.id, t2.idGroup