Join table twice - on two different columns of the same table
Solution 1
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 involve 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 also 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 - and 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
.
table3.id
needs to be UNIQUE
, or we might multiply rows with several matches from each LEFT JOIN
:
Solution 2
If you join a table several times, use aliases to distinguish them:
SELECT table1.id,table2.id,table2.table3_id_1,table2.table3_id_2,t3_1.id,t3_2.id
FROM table1
JOIN table2 ON table1.id=table2.table1_id
JOIN table3 t3_1 ON table2.table3_id_1=t3_1.id
JOIN table3 t3_2 ON table2.table3_id_2=t3_2.id
WHERE ... t3_1.id=... AND ... t3_2.id=...
Solution 3
select t1.id as table1_id,
t2.id as table2_id,
t2.table3_id_1,
t2.table3_id_2,
t3_1.value as X,
t3_2.value as Y
from Table1 t1
inner join Table2 t2 on t1.id = t2.table1_id
inner join Table3 t3_1 on t2.table3_id_1 = t3_1.id
inner join Table3 t3_2 on t2.table3_id_2 = t3_2.id
where t3_1.value = 'some_value'
or t3_2.value = 'some_other_value'
Red
Updated on July 09, 2022Comments
-
Red almost 2 years
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
andY
are the values for the row connected bytable3_id_1
andtable3_id_2
respectively.Possibly make them variables or something so I can filter them in a
WHERE
clause as well?