Reputation:

MySQL Conditional For Which Join to use

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.`bodyNo` = 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 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

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.

Upvotes: 1

Answers (4)

user5175034

Reputation:

There are likely other, better, answers but this works.

SELECT  COALESCE(m1.`seriesYear`, m2.`seriesYear`) AS seriesYear, 
        COALESCE(m1.`modelDesc`, m2.`modelDesc`) AS modelDesc, 
        COALESCE(m1.`bodyNo`, m2.`chassisNo`) AS bodyNo 
    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'

Upvotes: 1

MatBailie

Reputation: 86775

In various comments you state that you don't really care about performance, in which case you just want the simplest and most maintainable code?

So, just use an OR in the join predicate?

SELECT
  p.`baseGroup`,
  m.`seriesYear`,
  m.`modelDesc`,
  m.`bodyNo`,
  m.`chassisNo` AS modelNo 
FROM
  parts_parsed   p
LEFT JOIN
  parts_modelno  m
    ON p.`bodyNo`  = m.`bodyNo` 
    OR p.`modelNo` = m.`chassisNo` 
WHERE
  p.`partNo` = '$PartNos'

This assumes that p will never have both bodyNo and modelNo populated. If that's possible, you'll need to update the question with an example of the behaviour you desire.

As mentioned elsewhere, this code also means that indexes can not be used to optimise the query plan.

EDIT:

If performance does matter (and it should, even if you onoly mean cpu and disk utilisation rather than user experience), you should simply UNION two queries. This allows the optimiser to fully utilise indexes to avoid pointless reads, chose better join implementations, etc.

SELECT
  *
FROM
(
  SELECT
    p.`baseGroup`,
    p.`partNo`,
    m.`seriesYear`,
    m.`modelDesc`,
    m.`bodyNo`,
    m.`chassisNo` AS modelNo 
  FROM
    parts_parsed   p
  INNER JOIN
    parts_modelno  m
      ON p.`bodyNo`  = m.`bodyNo` 

  UNION ALL

  SELECT
    p.`baseGroup`,
    p.`partNo`,
    m.`seriesYear`,
    m.`modelDesc`,
    m.`bodyNo`,
    m.`chassisNo` AS modelNo 
  FROM
    parts_parsed   p
  INNER JOIN
    parts_modelno  m
      ON p.`modelNo` = m.`chassisNo` 
)
  part_list
WHERE
  `partNo` = '$PartNos'

The UNION ALL is preferred to UNION as that avoids UNION's deduplication overhead.

The net result is that only one or other sub-query will contain any results for any given part, and that each sub-query can use different indexes for optimised resource utilisation.

At the cost of longer code

Upvotes: 0

Stu

Reputation: 32614

Why not just use coalesce for the join?

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

Upvotes: -1

Stefan Avramovic

Reputation: 1353

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'

Upvotes: 0

MySQL Conditional For Which Join to use

Answers (4)

Related Questions