MySQL Conditional For Which Join to use-CodePudding

On a query, is it possible to conditionally choose which join to use? On the query below, m.bodyNo is sometimes empty with the join bringing up the data using m.chassisNo instead and I would like it to show results for whichever one has the values.

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'

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?

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.`modelNo` = m2.`bodyNo` 
    WHERE p.`partNo` = '$PartNos'

If this doesn't lose its formatting, it is a sample of parse_parts:

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

This is a sample of parts_modelno

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

CodePudding user response：

You can use a case method like this..

SELECT m1.`seriesYear`,
           m1.`bodyNo`,
           m1.`modelDesc`,
           m2.`seriesYear`,
           m2.`bodyNo`,
           m2.`modelDesc`
        CASE
            WHEN m2.value <> "" THEN m2.value
            ELSE m1.value
        END AS someName
    FROM parts_parsed p
    LEFT JOIN parts_modelno m1 ON p.`modelNo` = m1.`chassisNo`
    LEFT JOIN parts_modelno m2 ON p.`modelNo` = m2.`bodyNo`
    WHERE p.`partNo` = '$PartNos'

CodePudding user response：

Why not just use coalesce for the join?

LEFT JOIN parts_modelno m ON p.modelNo = coalesce(m.bodyNo, m.chassisNo)