CODEWITHSUNDEEP
sql
Nick
Nick

Reputation: 2980

Find supplier which supplies a product that others don't

I'm trying to write an SQL query which selects the supplier based on the fact that it can supply a product other suppliers cannot.

I have 2 columns: Supplier and Product

How would I select all the suppliers which supply at least 1 product which other suppliers do not supply?

I currently have:

SELECT incart.product, incart.supplier
FROM incart
WHERE incart.product 
HAVING count(incart.supplier)=1
;

Upvotes: 1

Views: 1569

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this:

SELECT 
  i1.supplier
FROM incart i1
WHERE i1.product NOT IN(SELECT product 
                        FROM incart i2
                        WHERE i1.supplier <> i2.supplier);

For example, for the following sample data:

| PRODUCT | SUPPLIER |
|---------|----------|
|       1 |        a |
|       2 |        b |
|       3 |        b |
|       2 |        c |
|       3 |        c |
|       4 |        c |

It will select suppliers a and c, because supplier a supplies product 1 which others don't, and supplier c supplies product 4 which others don't.

Upvotes: 2

Related Questions