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

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 don't know how to verify though that the suppliers have offered ALL products except those listed (computers, cars, tvs)

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)Try the following:

First create a query of the products you want to show.

SELECT Prod_ID, [Name]
FROM Tb_Products
WHERE Prod_ID NOT IN(10, 11, 12 [List of Prod_IDs not to include])

Once this query is created, create the new query using this query instead of Tb_Products|||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 --
select Supp_ID, count(*)
from Tb_Offers
group by Supp_ID
the total number of products is --
select count(*) from Tb_Products
the suppliers which supply all products are --
select Supp_ID
from Tb_Offers
group by Supp_ID
having count(*) =
( select count(*) from Tb_Products )
now for the tricky part, excluding three certain products

first, let's figure out which product IDs they have --
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
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 --
select Supp_ID
, sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
) then 1 else 0 end
) as excluded_product_count
from Tb_Offers
group by Supp_ID
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 --
select Supp_ID
from Tb_Offers
group by Supp_ID
having sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer' ,'car', 'tv')
) then 1 else 0 end
) = 0
and count(*) =
( select count(*) from Tb_Products ) - 3
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/|||CreativeSoul,

What you are after is called relational division.

Celko has an article that you will find very helpful...

http://www.dbazine.com/celko1.html

Please read this first and then look at this query...There are several approaches to achieving this in SQL...but this is usually the easiest to understand....

SELECT S.Supp_ID
FROM Tb_Supplier S
INNER JOIN Tb_Offers O on O.SUPP_ID = S.SUPP_ID
WHERE O.Prod_ID NOT IN(10,11,12)
GROUP BY S.Supp_ID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Tb_Product WHERE Prod_ID NOT IN(10,11,12))