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?
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:
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=...
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'