3 small table database. Query: Return All Suppliers, Offering All Product, Except-

I have broken my question down into simpler terms using only 3 small tables, but the idea is the same. Well let me get to the problem (=.

Pretend we have just a small
database with 3 tables (Tb_Supplier, Tb_Product,
Tb_Offers)
Our problem is as follows:
Write an SQL statement which returns ALL Supplier Names who Offer ALL Products EXCEPT computers, cars, and tvs.

Does anyone have any advice how this might be accomplished? Here is our tables..and what I have tried/thought of so far.

CREATE TABLE Tb_Supplier (
Supp_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)

CREATE TABLE Tb_Product (
Prod_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)

CREATE TABLE Tb_Offers (
Supp_ID [bigint] REFERENCES Tb_Supplier(Supp_ID) ,
Prod_ID [bigint] REFERENCES Tb_Product(Prod_ID) ,
Quantity [decimal](18, 0) NULL ,
Price [money] NULL
)

The query I'm trying to solve is to return ALL
supplier names, who "offer" ALL products, EXCEPT cars, computers, and tvs. **Without creating any new tables ora dding columns.

Here is what I have tried/ my thoughts. I first tried breaking it
into parts and seeing if I could solve them. For instance, I wanted
to return all suppliers NOT offering computers, cars, or tvs. I
accomplished that with the following query.

SELECT Name
FROM Tb_Supplier
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))

(also wrote it using the NOT IN statement)

SELECT Name
FROM Tb_Supplier
WHERE Supp_ID NOT IN
(SELECT DISTINCT Supp_ID
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))


I then tried selecting all suppliers who offer all products but I am lost as to what to do. It seems contradictory.

The only 3 tables that matter for this query are the Supplier, Product, and Offers Table. Here is what I need(in a small example).


Lets say we have 4 Suppliers. (Supp_ID's 111, 222, 333, and 444) (Names: Rick, Matt, Kelly, Steve) respectively

And we have 6 Products. (Prod_Id's 10, 11, 12, 13, 14, 15) (Names: cars, computers, tvs, soda, furniture, jewelry)

Our Offers Table contains the following

Supp_ID Prod_ID
111 10
111 13

222 11
222 13
222 14

333 13
333 14
333 15

444 14
444 15



I need to write a query which would return just those suppliers who are exactly like the Supplier (333). He offers ALL the products EXCEPT the computers, cars, and tvs.

I wouldn't want number 444, even though he doesn't offer computers, cars, or tvs...he still fails to offer all the other products by not offering #13 which is soda

I hope I am explaining this well. Any reply is greatly appreciated. Thanks!

(Oh and yes this is just Microsoft SQL Syntax)

this is a most interesting problem

let's assume that the primary key of Tb_Offers is (Supp_ID, Prod_ID)

in other words, a given supplier can offer a given product only once

(this is important because we'll be counting rows without using DISTINCT)

the number of products each supplier supplies is given by --

the total number of products is --

the suppliers which supply all products are --

now for the tricky part, excluding three certain products

first, let's figure out which product IDs they have --

now if a supplier supplies one of these three "excluded" products,
let's count a 1, and for any of the other products, let's count a 0 --

notice how the subquery inside the CASE is not correlated,
which means that it will be extremely efficient

the suppliers we want are those with an "excluded product count" of 0

furthermore, the count of all the products they do supply has to be
3 less than the total number of products

so here is the final query --

i'm fairly confident in this, but have not tested it

please let me know how it works for you


rudy
http://r937.com/
http://rudy.ca/