Conditional inner join statements in MySQL

conditional-statements, inner-join, mysql

Solution

Left / outer join to each of the three options and the conditional in your select will take care of the rest.

Perhaps something like:

...
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

Problem

Is there a way I can conditionally change which table i'm inner joining on based on the value of a field in another table? Here's what I got so far (but it errors) out: ``` 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 ``` So, there's a table (jobs) that has and idType (dealership, group, or agency) and a clientID (called dealershipID). What I need to do it join a certain table (based on idType) to determine which sales manager "owns" the client account. If idType = 'dealership' I need to join smdealershipjoins, if it's 'Group' i need to join smgroupjoins, and if it 'agency' I need to join smagencyjoins so that I can then join that table to userinfo in order to get the sales manager's name. I've also tried to use case statements to do the inner joining, but that gave an error too.

Original source