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

SELECT t2.table1_id     , t2.id          AS table2_id     , t2.table3_id_1     , t2.table3_id_2     , t31.value      AS x     , t32.value      AS yFROM   table2 t2LEFT   JOIN table3 t31 ON t31.id = t2.table3_id_1LEFT   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:

• Two SQL LEFT JOINS produce incorrect result

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.idFROM table1JOIN table2 ON table1.id=table2.table1_idJOIN table3 t3_1 ON table2.table3_id_1=t3_1.idJOIN table3 t3_2 ON table2.table3_id_2=t3_2.idWHERE ... 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 Yfrom Table1 t1inner join Table2 t2 on t1.id = t2.table1_idinner join Table3 t3_1 on t2.table3_id_1 = t3_1.idinner join Table3 t3_2 on t2.table3_id_2 = t3_2.idwhere t3_1.value = 'some_value'    or t3_2.value = 'some_other_value'