CODEWITHSUNDEEP
sql
Joril
Joril

Reputation: 20547

Join via junction table with nullable field

A "plain SQL question" :) I have two tables and a third one that acts as a "semi junction table", meaning that sometimes there's just one of the two foreign keys.. Is there a way to join them?

For example, given the following data:

Table D id's: 1,2,3,4,5
Table C id's: 1,2,3
Table Junction (D.id, C.id): (1,1) (2, NULL) (3, NULL) (4,2) (5,3)

is there a way to retrieve the following?
([D fields of row id 1], [C fields of row id 1])
([D fields of row id 2], [NULL C fields])
([D fields of row id 3], [NULL C fields])
([D fields of row id 4], [C fields of row id 2])
([D fields of row id 5], [C fields of row id 3])

If it's relevant, I'm using PostgreSQL.
Thanks for your time!

Upvotes: 1

Views: 956

Answers (1)

Quassnoi
Quassnoi

Reputation: 425351

SELECT  d.*, c.*
FROM    d
JOIN    junction j
ON      j.d_id = d.id
LEFT JOIN
        с
ON      c.id = j.c_id

Upvotes: 3

Related Questions