CODEWITHSUNDEEP
mysqljoin
Kaddath
Kaddath

Reputation: 6151

How to select multiple different colums and joins in a query depending on another column value?

I have an existing quite complicated request on table A where a JOIN is made on another tables B and C (and actually more).

I would like that on a specific value of a field in table A, fields are added to the SELECT but also different join(s).

I know that I can add columns dynamically with CASE, but the tested value is always the same, and I would like to avoid repeating a CASE for each field because there are many fields. (For clarity I named the tables "B" and "D" in the CASE but in the actual situation it could be the same with a join alias)

SELECT
  CASE
    WHEN A.a1=1 THEN B.b1
    WHEN A.a1=2 THEN D.d1
  END AS a2,
  CASE
    WHEN A.a1=1 THEN B.b2
    WHEN A.a1=2 THEN D.d2
  END AS a3,
  CASE
    WHEN A.a1=1 THEN B.b2
    WHEN A.a1=2 THEN D.d2
  END AS a3,
  //... many others on different tables but always on the same condition
FROM A
  JOIN //?? if first condition join on B and C else on D and E

Is there a solution to join dynamically and avoid this long repetition of CASE calls?

NOTE: this is only a part of a query built with code, which makes it difficult to use UNION because fields are added elsewhere and that would be difficult to match the same number of selected fields.

Upvotes: 0

Views: 24

Answers (1)

derek.wolfe
derek.wolfe

Reputation: 1116

You could use all LEFT JOINs with your current CASE conditions as part of the ON and then COALESCE the values of those tables. Something like:

      SELECT COALESCE(B.b1,D.d1) AS a2,
             COALESCE(B.b2,D.d2) AS a3,
             COALESCE(B.b3,D.d3) AS a4,
             ...
        FROM A
   LEFT JOIN B ON {normal_join_condition} AND A.a1 = 1
   LEFT JOIN C ON {normal_join_condition} AND A.a1 = 1
   LEFT JOIN D ON {normal_join_condition} AND A.a1 = 2
   LEFT JOIN E ON {normal_join_condition} AND A.a1 = 2

Upvotes: 1

Related Questions