tricky sql query - finding alternative supplier( relational division )

Question

This is a question I got from a book (don't remember which), it goes like this:

You have three tables:

Supplier (supId, name)
Product (prodId, name)
inventory (supId, prodId)

You need to find, with one query, all the suppliers that have in their inventory all the products (or more) supplier X has (lets say supplier X is the one with supId=1).

(so if supplier 1 has in his inventory bananas and apples, you need to find all the suppliers that carry at least bananas and apples)

You can use standard SQL only (including joins).

Apparently this is a known issue/question, you should check out this question: How to filter SQL results in a has-many-through relation (excellent solutions and analysis)

score 6 · Accepted Answer · answered Jan 14 '13 at 12:48

That problem is known as relational division.

One solution is double negation. You can select all suppliers, for whom no product delivered by supplier X exists, that is not delivered by them:

select  distinct other_supplier.SupID
from    Inventory other_supplier
where   not exists
        (
        select  *
        from    Inventory supplier_X
        where   supplier_X.supId = 1 -- For supplier X
                and not exists
                (        
                select  *
                from    Inventory other_product
                where   other_supplier.supId = other_product.Supid
                        and supplier_X.prodId = other_product.prodId
                )
        )

Live example at SQL Fiddle.

Minor minor detail, but "other_supplier" also lists "supplier_X". It is explicitly mentioned in the question, but doesn't fit your naming. — Jacco, Jan 14 '13 at 13:20
The link you provided is excellent! I wasn't aware of the name of the operation. thanks! — Ami Malimovka, Jan 14 '13 at 13:20

Jacco · Answer 2 · 2013-01-14T13:13:13.153

1

I believe this solution uses standard SQL, except for the parameter definition.

DECLARE @supplierX int = 4

SELECT 
  [s].[supid],
  [s].[name]
FROM [Inventory] [i1]
INNER JOIN [Inventory] [i2] ON [i1].[prodid] = [i2].[prodid]
INNER JOIN [Supplier] [s] ON [i1].[supid] = [s].[supid]
WHERE
  [i1].[supid] <> @supplierX
  AND [i2].[supid] = @supplierX
GROUP BY 
  [s].[supid],
  [s].[name]
HAVING 
  COUNT(*) >= (SELECT COUNT(*) FROM [Inventory] [i3] WHERE [i3].[supid] = @supplierX)

A Fiddle is found here.

A breakdown of the query above:

Determine the number of products supplierX has in it's inventory (count(*))
Determine the products other suppliers share with supplierX (join by prodid)
Make sure the number of shared products is higher than or equal to the number of products supplierX has in it's inventory (HAVING COUNT() >= ...)

edited Jan 14 '13 at 13:13

answered Jan 14 '13 at 13:07

Jacco

3,251
1
19
29

This is also a great answer, I wish I could accept both answers :-) – Ami Malimovka Jan 14 '13 at 13:24
I wish too! :-D I like @Andomar's answer too, because he didn't even need a join. At the cost of a nested "where not exists", but still. – Jacco Jan 14 '13 at 13:29
To be Standard SQL compliant (parameter/proedural code asise) you would need to terminate your statements with a semicolon and remove the square brackets (which are in this case redundant anyhow). – onedaywhen Dec 29 '14 at 10:23

tricky sql query - finding alternative supplier( relational division )

2 Answers2