CODEWITHSUNDEEP
sqlpostgresqlselectjoin
Aladine
Aladine

Reputation: 205

postgresql - Join tables on match but also ones that are not matching?

So I have a join and it works but its not showing me all the data I want. Currently I want to see every line in device_interface.c1 and if device_interface.c1 = device_inventory.c1 then I want to see that in a column as well. Right now I am only seeing what device_interface.c1 = device_inventory.c1.

Below is my query: 

SELECT 
  device_info.pk,
  device_interface.fk, 
  device_interface.c1, 
  device_interface.c2, 
  device_inventory.c1
FROM 
  "test".device_info, 
  "test".device_interface, 
  "test".device_inventory
WHERE 
  device_info.pk = device_interface.fk AND
  device_info.pk = device_inventory.fk AND
  device_interface.c1 = device_inventory.c1 AND
  device_interface.c2 = 'Physical'

Example Output:

device_info.pk  device_interface.c1   device_inventory.c1
1                        1                     1
1                        3                     3
1                        95                    95
2                        55                    55
2                        634                   634

Example of desired output:

device_info.pk  device_interface.c1   device_inventory.c1
1                        1                     1
1                        2          
1                        3                     3
1                        4
1                        5
1                        6
1                        95                    95
2                        55                    55
2                        56 
2                        57
2                        634                   634

Upvotes: 1

Views: 25

Answers (1)

Mureinik
Mureinik

Reputation: 311163

A left join should do the trick:

SELECT 
  device_info.pk,
  device_interface.fk, 
  device_interface.c1, 
  device_interface.c2, 
  device_inventory.c1
FROM 
  "test".device_info
LEFT JOIN
  "test".device_interface 
ON 
  device_info.pk = device_interface.fk
LEFT JOIN
  "test".device_inventory 
ON 
  device_info.pk = device_inventory.fk AND
  device_interface.c1 = device_inventory.c1
WHERE 
  device_interface.c2 = 'Physical'

Upvotes: 2

Related Questions