CODEWITHSUNDEEP
sqlpostgresqlselectjoin
Jon
Jon

Reputation: 6265

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

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

Upvotes: 3

Views: 9780

Answers (2)

Mureinik
Mureinik

Reputation: 311326

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 = ?

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 2

Related Questions