我在sqlite中有两个表:
名称
id name
--- -----
1 Tyrion
2 Arya
3 Snow
信息
id otherID
--- ---------
2 1
3 2
1
如何获得此结果
2, Arya, 1, Tyrion
3, Snow, 2, Arya
1, Tyrion
如果我进行此查询:
SELECT info.id, names.name, info.otherID, names2.name
FROM info
inner join names on names.id = info.id
inner join names as names2 on names2.id = info.otherID
我只得到这个:
2, Arya, 1, Tyrion
3, Snow, 2, Arya
没有显示:
1, Tyrion
如何获取可选字段otherid的列表
左联接就是答案。
SELECT info.id, names.name, info.otherID, names2.name
FROM info
LEFT join names on names.id = info.id
LEFT join names as names2 on names2.id = info.otherID
您希望拥有info
(联接语句中的左表)的所有行,而不管names
表(联接语句的右侧)中是否有条目。
尝试使用LEFT JOIN
SELECT info.id, names.name, info.otherID, names2.name
FROM info
LEFT JOIN names on names.id = info.id
LEFT JOIN names as names2 on names2.id = info.otherID