有条件的内部连接在MySQL声明(Conditional inner join statements

有没有一种方法，我可以有条件地改变我的基于字段的另一个表中的值是内侧连接上表？ 这是我走到这一步（但错误）了：

SELECT 
    j.jobID, j.jobNumber,
    CASE
        WHEN j.idType = 'dealership' THEN d.dealershipName
        WHEN j.idType = 'Group' THEN g.groupName
        WHEN j.idType = 'Agency' then a.agencyName
    END as dealershipName,
    CASE
        WHEN p.manualTimestamp != '0000-00-00 00:00:00' THEN UNIX_TIMESTAMP(p.manualTimestamp)
        WHEN p.manualTimestamp = '0000-00-00 00:00:00' THEN p.timestamp
    END as checkTS,
    CONCAT_WS(' ', ui.fName, ui.lName) as salesRep
FROM jobs j
LEFT JOIN dealerships d ON j.dealershipID = d.dealershipID
LEFT JOIN dealershipgroups g ON j.dealershipID = g.groupID
LEFT JOIN agencies a ON j.dealershipID = a.agencyID
INNER JOIN payments p ON j.jobID = p.jobID
IF j.idType = 'dealership' THEN 
    INNER JOIN smdealershipjoins smdj ON j.dealershipID = smdj.dealership
    INNER JOIN userinfo ui ON smdj.sm = ui.userID           
ELSEIF j.idType = 'Group' THEN 
    INNER JOIN smgroupjoins gj ON j.dealershipID = gj.groupID
    INNER JOIN userinfo ui ON gj.sm = ui.userID
ELSEIF j.idType = 'Agency' THEN 
    INNER JOIN smagencyjoins aj ON j.dealershipID = aj.agencyID
    INNER JOIN userinfo ui on aj.sm = ui.userID
END IF

所以，有，有一个表（作业）和idType（经销商，组或机构）和一个clientId（称为dealershipID）。 我需要做的是加入了一定的表（基于idType），以确定哪些销售经理“拥有”客户的帐户。 如果idtype =“经销商”我需要加入smdealershipjoins，如果它是“集团”我需要加入smgroupjoins，如果“机构”我需要加入smagencyjoins，这样我就可以加入该表，以获得销售用户信息经理的名字。

我也试图用case语句做内侧连接，但也给了一个错误。

Answer 1:

左/外连接到每个三种选择，并在您的选择将负责其余的条件。

也许是这样的：

...
LEFT JOIN smdealershipjoins smdj 
    ON j.dealershipID = smdj.dealership
LEFT JOIN smgroupjoins gj 
    ON j.dealershipID = gj.groupID
LEFT JOIN smagencyjoins aj 
    ON j.dealershipID = aj.agencyID
LEFT JOIN userinfo ui 
    ON ui.userID = CASE
        WHEN j.idType = 'dealership' THEN smdj.sm
        WHEN j.idType = 'Group' THEN gj.sm
        WHEN j.idType = 'Agency' then aj.sm
    END

文章来源: Conditional inner join statements in MySQL