Join table twice - on two different columns of the same table
foreign-keys, join, left-join, postgresql, sql
Solution
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`:
- Two SQL LEFT JOINS produce incorrect result
Problem
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?