I hope this is the right form to ask this question...if not I apologize, I am a novice here. Well let me get to the problem (=.

We have just a practice
database with 6 tables (Tb_Supplier, Tb_Consumer, Tb_Product,
Tb_Offers, Tb_Requests, and Tb_Transactions)
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.

I'll show you what I've "attempted so
far. But here are the created tables first, of course we later added
primary/foreign key constraints.

Code:

CREATE TABLE [dbo].[Tb_Consumer] (
[Con_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Name] [char] (10) NOT NULL ,
[City] [char] (15) NULL
)
GO

CREATE TABLE [dbo].[Tb_Offers] (
[Supp_ID] [bigint] NOT NULL ,
[Prod_ID] [bigint] NOT NULL ,
[Quantity] [decimal](18, 0) NULL ,
[Price] [money] NULL
)
GO

CREATE TABLE [dbo].[Tb_Product] (
[Prod_ID] [bigint] IDENTITY (1, 1) NOT NULL,
[Name] [char] (10)NOT NULL ,
[MU] [char] (7) NULL
)
GO

CREATE TABLE [dbo].[Tb_Requests] (
[Con_ID] [bigint] NOT NULL ,
[Prod_ID] [bigint] NOT NULL ,
[Quantity] [decimal](18, 0) NULL ,
[Price] [money] NULL
)
GO

CREATE TABLE [dbo].[Tb_Supplier] (
[Supp_ID] [bigint] IDENTITY (1, 1) NOT NULL,
[Name] [char] (10) NOT NULL ,
[City] [char] (15) NULL
)
GO

CREATE TABLE [dbo].[Tb_Transactions] (
[Tran_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Supp_ID] [bigint] NOT NULL ,
[Con_ID] [bigint] NOT NULL ,
[Prod_ID] [bigint] NOT NULL ,
[Quantity] [decimal](18, 0) NULL ,
[Price] [money] NULL
)
GO

We then added primary/foreign key constraints. For the Supplier,
Consumer, and Product tables...the primary key(s) are Supp_ID, Con_ID,
and Prod_Id respectively.
For the Offers Table, it is linked to the supplier and product tables
through its two foreign keys. (Supp_ID, and Prod_ID)
For the Requests Table, it is linked to the consumer and product
tables through its two foreign keys (Con_ID and Prod_ID)
And lastly, the Transactions table is linked to the Supplier,
Consumer, and Product tables through foreign keys (Supp_ID, Con_ID,
and Prod_ID)

The query I'm trying to solve is to return ALL
supplier names, who offer ALL products, EXCEPT cars, computers, and
tvs.

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.

Code:

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'))

Then I wrote the query to return the list of the suppliers who offer
all products.

Code:

SELECT Name
FROM Tb_Supplier S
WHERE NOT EXISTS(SELECT *
FROM Tb_Product P
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers
WHERE S.Supp_ID=Tb_Offers.Supp_ID
AND Prod_ID=P.Prod_ID))

Now here is where I am stuck. I can't just simply combine the queries
with an "and" as follows:

Code:

SELECT Name
FROM Tb_Supplier S
WHERE NOT EXISTS(SELECT *
FROM Tb_Product P
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers
WHERE S.Supp_ID=Tb_Offers.Supp_ID
AND Prod_ID=P.Prod_ID))
AND 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'))

This is contradictory since I first select all suppliers offering ALL
products (meaning they offer everything INCLUDING computers, tvs, and
cars) while I leave out the suppliers who I DO want, who offer
everything EXCEPT computers, tvs, and cars. This query I came up with
always returns nothing.

I have been working on this solution and trying everything. I just
can't figure it out )=. Please help. I could use some advice. Am I going about it all wrong?

I assume you are using SQL Server for your RDMS? Helps with the syntax of the SQL statements since each RDMS has its own set of syntax....

Anyway, have you tried the following:


Basically, using the INNER JOIN function will join the three necessary tables, and uses the <> operator as NOT to make sure that the Product Name does not include those keywords.

Hope this helps.
========================================
Buh Bye!

Cheers,
Me

Thanks for the advice guy, I'll give it a try. I need to run to a different machine to test it out. I'm not sure if it will return entirely what I need though. I think it leaves out the part about the suppliers needing to offer "ALL products"...except (computers, cars, and tvs). I probably explained the problem incorrectly, I apologize but I think the idea is the same.

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

Tb_Supplier: Has the Supp_ID column as its primary key, and also lists the names of the corresponding suppliers

Tb_Product: Has a Prod_ID column as it's primary key. And Lists the name of all the corresponding products.

Tb_Offers: Has 2 foreign keys (Supp_ID and Prod_ID) which link it to the Supplier and Product tables. So it lists the Supplier_ID with the Prod_ID that the supplier is offering. (Also lists quantity and price which we don't care about for this query)

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 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)

Well, I'd try running suggested queries first before questioning the suggestion...If the query didn't work, okay, but you stated,"I'll give it a try"....which indicates that you have _not_ tried the query. In order to save time and frustration, why not test suggested codes/queries out first, then if they don't work, we can work together to find another solution.
========================================
Buh Bye!

Cheers,
Me

I apologize, Stealth, for not running it first. I can't access the database from home here, so I was trying to sort of play the code in my head. But you're right, I should have definitely given it a test before thinking negatively. I'll give it a run tomorrow and let you know the results.

I really do appreciate the help and suggestions. Thanks again.

Stay Well,

BluesCafe

One thing to note...since you might be joining on the Tb_Offers table, just replace the Tb_Transactions references in the query I provided, and it will do the same thing as you want. It was difficult to tell from your table properties exactly which intersection table you wanted to use in your query.
========================================
Buh Bye!

Cheers,
Me

Hey Stealth,

I ran the query:

SELECT S.Name, P.Name
FROM Tb_Supplier S INNER JOIN (Tb_Product P INNER JOIN Tb_Offers O ON P.Prod_ID = O.Prod_ID
) ON S.Supp_ID = O.Supp_ID
WHERE (P.Name <> 'computer')
OR (P.Name <> 'car')
OR (P.Name <> 'tv')



It results in returning all rows in the offers table, even the rows where they offer computers, tvs, or cars.

I'll play around with it a bit, if you know anymore ideas I can try please let me know. Thanks!

Phew, I think I may have found a solution that works. It appears to with the data I've run so far...unless there is a glitch I'm not seeing.

Select Distinct S.Name
From Tb_Supplier S, Tb_Product P, Tb_Offers O
Where S.Supp_ID = O.Supp_ID
AND O.Prod_ID = P.Prod_ID
AND S.Supp_ID Not In (Select Distinct Supp_ID
From Tb_Offers, Tb_Product
Where Tb_Product.Prod_ID = Tb_Offers.Prod_ID
AND (Tb_Product.Name = 'computer'
OR Tb_Product.Name = 'car'
OR Tb_Product.Name = 'tv'))
Group By S.Name
Having Count(Distinct P.Name) = (Select Count(*) From Tb_Product
Where (Tb_Product.Name <> 'computer' AND Tb_Product.Name <> 'tv' AND Tb_Product.Name<>'car'))