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.