Postgresql multiple joins in single query where foreign key of a join doesn't exist in all tables

user_4086590 0
Viewed 7973

Is it possible to have two joins in a single query, where the second join is a connection between table_2 and table_3 (no key references in table_1)?

table_1

id | column_a



table_2

id | table_1_id | table_3_id | column_b



table_3

id | column_c

Existing Query:

SELECT * FROM table_1 RIGHT OUTER JOIN table_2 WHERE table_1.id id = ? and WHERE column_a = ?

Gives me the info I want from table_1 and table_2, but table_2's info will have just the table_3_id column.

In the same query, I'd like to join table_3 to get its data based on table_2.table_3_id

select join postgresql sql
2 Answers

In a word - yes. You can just add more join clauses:

SELECT           * 
FROM             table_1 
RIGHT OUTER JOIN table_2 ON table_1.table_2_id = table_2.id
RIGHT OUTER JOIN table_3 ON table_2.table_3_id = table_3.id
WHERE            table_1.id = ? AND column_a = ?
user_24227760

You where clause is turning the right outer join into an inner join. I'm not a fan of right joins in general. I think the semantics of a left join are simpler.

So, you should write this as:

SELECT . . .
FROM table_1 t1 JOIN
     table_2 t2
     ON t2.table1.id = t1.id JOIN
     table_3 t3
     ON t2.table3.id = t3.id
WHERE t1.id = ? AND column_a = ?;

Notes:

  • You should not use outer join and then undo them in the where clause.
  • You should list the columns you want explicitly.
  • You should qualify all column references in a multi-table query.
  • Table aliases make the query easier to write and to read.
user_11440350
Related