CODEWITHSUNDEEP
mysqlsql
cfardella
cfardella

Reputation: 89

JOIN two tables on unknown column and return that column name in the result

I have 2 tables:

table_a fields:

table_a_id
table_a_name
table_a_reg
table_a_id table_a_name table_a_reg
A1 NAME_1 REG_1
A2 NAME_2 REG_2
A3 NAME_3 REG_3

table_b fields:

table_b_id
table_b_name
table_b_set_1
table_b_set_2
table_b_set_3

table_a_id can be in a row (only ever in 1 row) in either table_b_set_1, table_b_set_2 or table_b_set_3

table_b_id table_b_loc table_b_set_1 table_b_set_2 table_b_set_3
B1 LOC_1 A1 Z1 X1
B2 LOC_2 Z2 A2 X2
B3 LOC_3 A3 Z3 X3

I need to create a VIEW that shows:

table_a_id
table_a_name
table_a_reg
table_b_id
table_b_loc

AND I need to have an additional field in that VIEW tells which of table_b_set_1, table_b_set_2 or table_b_set_3 that table_a_id was in. So resulting VIEW would look like:

table_a_id table_a_name table_a_reg table_b_id table_b_loc table_b_set
A1 NAME_1 REG_1 B1 LOC_1 table_b_set_1
A2 NAME_2 REG_2 B2 LOC_2 table_b_set_2
A3 NAME_3 REG_3 B3 LOC_3 table_b_set_1

So far I can select the data but only where I specify the table_b_set in the JOIN (and that limits me to only the data from that set only):

SELECT
    a.table_a_id, a.table_a_name, a.table_a_reg, b.table_b_id, b.table_b_loc
FROM
    (
    SELECT table_a_id, table_a_name, table_a_reg
    FROM tables_a
    ) a 
    JOIN table_b b ON a.table_a_id = b.table_b_set_1

To be honest I am stuck here and don't know whether I should be considering a different approach.

Any advice would be appreciated!

Upvotes: 1

Views: 144

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You can join using in:

SELECT a.table_a_id, a.table_a_name, a.table_a_reg,
       b.table_b_id, b.table_b_loc
FROM table_a a JOIN
     table_b b
     ON a.table_a_id IN (b.table_b_set_1, b.table_b_set_2, b.table_b_set_3);

For the last part, you can use a case expression:

SELECT a.table_a_id, a.table_a_name, a.table_a_reg,
       b.table_b_id, b.table_b_loc,
       (case when a.table_a_id = b.table_b_set_1 then 'table_b_set_1'
             when a.table_a_id = b.table_b_set_2 then 'table_b_set_2'
             when a.table_a_id = b.table_b_set_3 then 'table_b_set_3'
        end) as which   
FROM table_a a JOIN
     table_b b
     ON a.table_a_id IN (b.table_b_set_1, b.table_b_set_2, b.table_b_set_3);

Upvotes: 1

Related Questions