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)

Answer 1

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.

Answer 2

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() >= ...)