Get supplier names for suppliers who do not supply part P2

[polaryeti]

This is a sample database.

The answer(relational algebra query) is:(I am converting those queries to SQL)

So it is first finding all suppliers who supply P2 from shipments table

Then it finds all suppliers who do not supply p2.

Then it joins all those suppliers who do not supply p2 with suppliers table? What does this gives us? A join with those who do not supply part p2?

Now, finally, it subtracts that joined result-thosee who supply p2.

And that should bring the names of suppliers who do not supply p2.

I did not quite get how join helped here? Can anyone describe this?

[polaryeti]

Personally I would frame a query like this

select supplier_name from suppliers join
shipments
(select supplier_number from shipments
where part_number!='P2')
DIFFERENCE
(select supplier_number from shipments
where part_number='P2'
)

But obviously there are lots of mistakes here since I am not aware of what difference operator is in postgresql,how can I rename a table

 

[Barand]

To find records where there is no match in another table you use a LEFT JOIN. Data from the the second table where there is no match will contain NULL values.

SELECT s.name
FROM supplier s
     LEFT JOIN shipment sh
          ON s.suppid = sh.suppid
          AND sh.prodid = 'P2'
WHERE sh.suppid IS NULL