title: Conditional inner join statements in MySQL
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>I've also tried to use case statements to do the inner joining, but that gave an error too.</p>