CODEWITHSUNDEEP
postgresqlleft-joinouter-join
Gregory West
Gregory West

Reputation: 149

I am having trouble with my SQL query in PostgreSQL

What I am trying to do is get all records from parts and any records from oelineitem where parts.id is equal owlineitems.part_id as well I need a couple fields from the table oe.

I am guaranteed a row in oe for every row in oelineitems (many-to-one relation) but I am not guaranteed a row in oelineitem for every row in parts. Even if there is no row in oelineitem, I need the row from parts to be in the dataset.

My query:

SELECT parts.partnumber, 
       parts.description, 
       parts.bin, 
       parts.obsolete, 
       orderitems.qty, 
       oe.transdate 
FROM   parts 
LEFT JOIN orderitems 
       ON parts.id = orderitems.parts_id
LEFT JOIN oe 
       ON orderitems.trans_id = oe.id 
WHERE  oe.customer_id <> 12228
ORDER  BY part.partnumber;

Upvotes: 0

Views: 42

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Move the criteria from WHERE to ON:

SELECT parts.partnumber, 
       parts.description, 
       parts.bin, 
       parts.obsolete, 
       orderitems.qty, 
       oe.transdate 
FROM   parts 
LEFT JOIN orderitems 
       ON parts.id = orderitems.parts_id
LEFT JOIN oe 
       ON orderitems.trans_id = oe.id 
       AND oe.customer_id <> 12228
ORDER  BY part.partnumber;

You are outer-joining records. So in case there is no match you join a dummy record with all columns NULL. If you ask WHERE oe.customer_id <> 12228 you dismiss these records, so you remove all outer-joined records and are where you would have been with a mere inner join.

(Because NULL <> 12228 results in null, not in true.)

Upvotes: 2

Related Questions