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.