MySQL Left Join(我认为)困难

Question

我有以下查询:

SELECT 
    u.username as username, 
    s.campaignno as campaign, 
    if(f.hometeamscore>f.awayteamscore,1,0) as Win, 
    if(f.hometeamscore=f.awayteamscore,1,0) as Draw, 
    if(f.hometeamscore<f.awayteamscore,1,0) as Loss, 
    f.hometeamscore as Goals, 
    ss.seasonid as Season, 
    av.avatar as Avatar 
FROM 
    avatar_avatar av, 
    straightred_fixture f, 
    straightred_userselection s, 
    auth_user u, 
    straightred_season ss 
WHERE 
        av.user_id = u.id 
    AND ss.seasonid = 1025 
    AND f.soccerseasonid = ss.seasonid 
    AND s.fixtureid = f.fixtureid 
    AND s.teamselectionid = f.hometeamid 
    AND s.user_id = u.id;

此查询按预期工作，但我现在意识到用户可能尚未上传个人资料图片。因此，以下部分 av.user_id = u.id 排除任何未上传个人资料图片的人。在阅读以下内容后，我觉得我需要使用左连接 https://www.w3schools.com/sql/sql_join.asp 但我只是一直在兜圈子，却一无所获。

对此的任何指导将不胜感激，非常感谢，艾伦。

Answer 1

首先也是最重要的:避免隐式JOIN。使 JOIN 明确，您将更清楚哪个实体与哪个实体相关，并且您永远不会忘记在 中添加 AND 条件之一WHERE 并获得笛卡尔积。

第二:尝试使用遵循特定逻辑的顺序将表放入 FROM 中。在您的情况下，您似乎开始寻找 ss.seasonid = 1025...(这是 WHERE 具有常量的唯一条件)。然后，您的条件列表会产生一定的逻辑顺序...FROM 中的每个表都与前一个表有关系...

也就是说，我认为您需要这个查询:

SELECT 
    u.username as username, 
    s.campaignno as campaign, 
    if(f.hometeamscore>f.awayteamscore,1,0) as Win, 
    if(f.hometeamscore=f.awayteamscore,1,0) as Draw, 
    if(f.hometeamscore<f.awayteamscore,1,0) as Loss, 
    f.hometeamscore as Goals, 
    ss.seasonid as Season, 
    av.avatar as Avatar 
FROM
    straightred_season ss 
    JOIN straightred_fixture f 
        ON f.soccerseasonid = ss.seasonid  
    JOIN straightred_userselection s 
        ON s.fixtureid = f.fixtureid AND s.teamselectionid = f.hometeamid 
    JOIN auth_user u 
        ON u.id = s.user_id
    -- This last table is the one that needs to be LEFT-joined
    -- if the avatar is *optional*. If it isn't there, av.avatar will just
    -- be shown as NULL
    LEFT JOIN avatar_avatar av 
        ON av.user_id = u.id  
WHERE 
    ss.seasonid = 1025 ;

如果多个表的内容可选，您可能需要多个LEFT JOIN。为了找出有意义的内容，我们需要拥有代表您的场景的完整数据模型或 ERD。即哪些关系是1对1，哪些是1对多，哪些是1对(0或1)，哪些是多对多等。