Red Red - 3 months ago 6
SQL Question

Join table twice - on two different columns of the same table

I have a very confusing database with a table that holds two values I need in a separate table. Here is my issue:

Table1
- id

Table2
- id
- table1_id
- table3_id_1
- table3_id_2

Table3
- id
- value


I need to go from table1 and do a join that would give me back the value from
table3
in two separate columns. So I want something like this:

table1.id | table2.id | table2.table3_id_1 | table2.table3_id_2 | X | Y


Where
X
and
Y
are the values for the row connected by
table3_id_1
and
table3_id_2
respectively.

Possibly make them variables or something so I can filter them in a
WHERE
clause as well?

Answer
SELECT t2.table1_id
     , t2.id          AS table2_id
     , t2.table3_id_1
     , t2.table3_id_2
     , t31.value      AS x
     , t32.value      AS y
FROM   table2 t2
LEFT   JOIN table3 t31 ON t31.id = t2.table3_id_1
LEFT   JOIN table3 t32 ON t32.id = t2.table3_id_2;

There is no need to join in table1. table2 has all you need - assuming there is a foreign key constraint guaranteeing referential integrity (all t2.table1_id are actually present in table1). Else you may want to join to table1, thereby selecting only rows present in table1.

I use LEFT [OUTER] JOIN (and not [INNER] JOIN) to join to both instances of table3 for a similar reason: it is unclear whether referential integrity is guaranteed - ans whether any of the key columns can be NULL. An [INNER] JOIN would drop rows from the result where no match is found. I assume you would rather display such rows with a NULL value for any missing x or y.

And table3.id needs to be UNIQUE, or we might multiply rows with several matches from each LEFT JOIN:

Comments