SQL三连接表中的某些行可能不存在
问题描述:
我有1个表包含主要行,我希望所有这些行。然后有两个表可能没有匹配的行,但如果这些表没有匹配的行,我仍然需要来自主表的行。SQL三连接表中的某些行可能不存在
该查询有效,但是如果在所有3个表中没有一行,则不会返回任何结果。我使用MySQL与PHP。
SELECT PS_Info.* , PS_Guides.Guide, PS_User.UserID FROM PS_Info
JOIN PS_Guides ON PS_Info.ID = PS_Guides.InfoID
JOIN PS_User ON PS_Info.ID = PS_User.InfoID
WHERE PS_Info.GameID = :ID AND PS_User.UserID = :UserID
我想显示在PS_Info中的列表。然后,如果用户将它们设置为隐藏(PS_User = UserID,InfoID),我想隐藏这些项目中的一部分。如果PS_User中没有任何内容,那么他们没有设置这个项目被隐藏,所以我想显示它。 PS_Guides包含有关列表中每个项目的额外信息。并非所有的项目都包含额外的信息,所以如果这个表格中没有行,那么我就不会显示它。
答
SELECT PS_Info.* , PS_Guides.Guide, PS_User.UserID FROM PS_Info
left JOIN PS_Guides ON PS_Info.ID = PS_Guides.InfoID //use left join
left JOIN PS_User ON PS_Info.ID = PS_User.InfoID //use left join
WHERE PS_Info.GameID = :ID AND PS_User.UserID = :UserID
+0
这解决了问题,但不完全。我还必须添加WHERE PS_Info.GameID =:ID和PS_User.UserID =:用户ID或PS_User.UserID IS NULL – 2014-12-07 14:56:42
答
您正在寻找'left join'。
SELECT PS_Info.* , PS_Guides.Guide, PS_User.UserID FROM PS_Info
LEFT JOIN PS_Guides ON PS_Info.ID = PS_Guides.InfoID
LEFT JOIN PS_User ON PS_Info.ID = PS_User.InfoID
WHERE PS_Info.GameID = :ID AND PS_User.UserID = :UserID
完美!谢谢! – 2014-12-07 13:05:39