Question 215352 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Left joining on one of multiple columns from right table, depending on value from the left table

I have two tables which I need to join: Table A, which has to be the leftmost table, and Table B, which must be joined onto it via LEFT JOIN. I cannot use a UNION in this scenario.

Table A:

ID Data
1 blahblahblah
2 yadayadayada
3 whatever

Table B:

Primary_ID Secondary_ID More_Data
1 3 etc etc
4 3 doesn’t matter

The ideal way for the join to work is to join on the primary ID first, and if there is no match, then to join on the secondary ID.

Joined Results:

A.ID A.Data B.Primary_ID B.Secondary_ID B.More_Data
1 blahblahblah 1 3 etc etc
2 yadayadayada
3 whatever 4 3 doesn’t matter

An OR clause could do the trick:

SELECT *
FROM A
LEFT JOIN B ON A.ID = B.Primary_ID OR A.ID = B.Secondary_ID

however it is very inefficient on a larger scale.

Another option is to break it out into two joins and coalesce the results:

SELECT A.*, 
       COALESCE(B1.Primary_ID, B2.Primary_ID),
       COALESCE(B1.Secondary_ID, B2.Secondary_ID),
       COALESCE(B1.More_Data, B2.More_Data), 
FROM A
LEFT JOIN B1 ON A.ID = B.Primary_ID
LEFT JOIN B2 ON A.ID = B2.Secondary_ID

But this is not sustainable on the scale I am working with.

So, is there any way of achieving the results without a UNION, or an OR clause in the join statement, or by breaking it out into multiple joins?

Tags:

2

Answers


  1. 0 
    SELECT
A.ID,
A.Data,
B.Primary_ID,
B.Secondary_ID,
B.More_Data
FROM
TableA A
LEFT JOIN (
SELECT Primary_ID, Secondary_ID, More_Data
FROM TableB
UNION
SELECT Primary_ID, Secondary_ID, More_Data
FROM TableB
) B ON A.ID = B.Primary_ID OR A.ID = B.Secondary_ID;
    Login or Signup to reply.
  2. 0

    I understand your concern with the join with OR in predicate as this could lead to an inneficient join.

    But I do not understand why the two join option with coalesce is not sustainable – I would follow this approach.

    Anyway if you are on the search for further variations bellow is one example – checking first which key (primary / secondary) should be used and performing than simple outer join on single id

    with one_id_tb as (
select 
 coalesce(ta.id,ta2.id) id,
 Primary_ID, Secondary_ID,
 More_Data
from tb
left outer join ta on tb.Primary_ID = ta.id
left outer join ta ta2 on tb.Secondary_ID = ta2.id
)
select 
  ta.id a_id, ta.data a_data, Primary_ID, Secondary_ID,More_Data
from ta 
left outer join one_id_tb
on ta.id = one_id_tb.id

a_id|a_data      |primary_id|secondary_id|more_data     |
----+------------+----------+------------+--------------+
   1|blahblahblah|         1|           3|etc etc       |
   2|yadayadayada|          |            |              |
   3|whatever    |         4|           3|doesn't matter|
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search