title: MySQL Conditional For Which Join to use
body: <p>On a query, is it possible to conditionally choose which join to use? On the query below, <code>m.bodyNo</code> is sometimes empty with the join bringing up the data using <code>m.chassisNo</code> instead and I would like it to show results for whichever one has the values.</p>
  <pre><code>SELECT m.`seriesYear`, m.`bodyNo`, m.`modelDesc` 
  FROM parts_parsed p
      LEFT JOIN parts_modelno m ON p.`modelNo` = m.`bodyNo` 
      WHERE p.`partNo` = '$PartNos'
  </code></pre>
  <p>Trying two joins brings up all the data in my query tester (HeidiSQL) and it gives the same name for the relative columns of the two joins but would require providing aliases for each value and doing lots of conditionals in the PHP so I am looking for something more direct. Maybe something like COALESCE would do it but not sure how to apply it. Can anyone advise on how to proceed?</p>
  <pre><code>SELECT m1.`seriesYear`, m1.`bodyNo`, m1.`modelDesc`, m2.`seriesYear`, m2.`bodyNo`, m2.`modelDesc`  
      FROM parts_parsed p
      LEFT JOIN parts_modelno m1 ON p.`modelNo` = m1.`chassisNo` 
      LEFT JOIN parts_modelno m2 ON p.`bodyNo` = m2.`bodyNo` 
      WHERE p.`partNo` = '$PartNos'
  </code></pre>
  <p>If this doesn't lose its formatting, it is a sample of parse_parts:</p>
  <pre><code>ID  pageNo  baseGroup   partID  partNo  modelNo bodyNo  isRHD
  1       1       0         1     391906  2201              0
  2       1       0         1     391906  2202              0
  3       1       0         1     391906  2211              0
  4       1       0         1     391906  2220              0
  5       1       0         1     391906  2222              0
  43493   315     30      9983    386405            2250    0
  43494   315     30      9983    386405            2251    0
  43495   315     30      9983    386405            2270    0
  43496   315     30      9983    386405            2271    0
  43497   315     30      9984    438463            2650    0
  </code></pre>
  <p>This is a sample of parts_modelno</p>
  <pre><code>ID  seriesYear  bodyNo  chassisNo   engineNo    modelDesc
  1   1948-49     2213        2213    G600000    HEARSE AND AMBULANCE
  2   1948-49     2250        2226    G600000    7 PASS, CUSTOM EIGHT LIMOUSINE
  3   1948-49     2251        2226    G600000    7 PASS, CUSTOM EIGHT SEDAN
  4   1948-49     2252        2206    G600000    6 PASS, CUSTOM EIGHT SEDAN
  5   1948-49     2255        2206    G600000    6 PASS, CUSTOM EIGHT CLUB SEDAN
  6   1948-49     2259        2233    G600000    6 PASS, CUSTOM EIGHT CONVERTIBLE VICTORIA
  7   1948-49     2262        2211    G200000    6 PASS, DELUXE EIGHT TOURING SEDAN
  8   1948-49     2265        2211    G200000    6 PASS, DELUXE EIGHT CLUB SEDAN
  9   1948-49     2270        2222    G400000    7 PASS, DELUXE SUPER EIGHT LIMOUSINE
  10  1948-49     2271        2222    G400000    7 PASS, DELUXE SUPER EIGHT SEDAN
  </code></pre>
  <p>parts_modelno has fully populated values in all rows for both bodyNo and chassisNo columns while parts_parts has either bodyNo or modelNo with a few exceptions having both but in those few cases, the two values are the same. parts_modelno.chassisNo matches parts_parsed.modelNo.</p>