CODEWITHSUNDEEP
sql
UserName
UserName

Reputation: 123

How does <> work in SQL?

I have a mock exam and one of the questions is

Which supplier name has not shipped a red part?

The Database schema is as follows 

 Supplier (SupplierName, City) 
 Part(PartName, Color, Weight) 
 Shipment(SupplierName, PartName, Date) 
 Shipment(SupplierName) is a F.K. onto Supplier(SupplierName) 
 Shipment(PartName) is a F.K. onto Part(PartName)

The query I have come up with is

SELECT S.SupplierName
FROM Supplier as S
JOIN Shipment as SH on S.SupplierName = SH.SupplierName
JOIN Part as P on SH.PartName = P.PartName
WHERE P.Color <> 'Red'

My question is, am I using <> the right way?

Upvotes: 1

Views: 84

Answers (1)

Martin Smith
Martin Smith

Reputation: 453298

No you are finding suppliers that have supplied at least one part that isn't red.

They may also have supplied red parts even in the same shipment as non red ones.

You could use 

SELECT S.SupplierName
FROM   Supplier AS S
WHERE  NOT EXISTS (SELECT *
                   FROM   Shipment AS SH
                          JOIN Part AS P
                            ON SH.PartName = P.PartName
                   WHERE  S.SupplierName = SH.SupplierName
                          AND P.Color = 'Red')

Upvotes: 2

Related Questions