Basic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Basic SQL

Author

Topic

jsaetrum
Starting Member

7 Posts

Posted - 2011-09-15 : 16:07:15

Hi.

I'm new to SQL, and I'm struggling with finding an expression for the following:

"Make a list of customers who have ordered all products that customer 20 has ordered."

Any hints, what operators I need to use? I have tried to extract the products the customers have and test for whether 20's products are a subset of the customers'. But without success.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-15 : 16:14:55

Show us what you have so far. We can help you with your homework questions, but we need to see some effort from you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jsaetrum
Starting Member

7 Posts

Posted - 2011-09-15 : 16:26:56

Well, first I tried to divide it in two relations, like:

"Which products does each customer have?"

SELECT DISTINCT Customers.customerName, Products.productName
FROM Products, OrderDetails, Orders, Customers
WHERE P.productCode = OrderDetails.productCode and
OrderDetails.orderNumber = Orders.orderNumber and
Orders.customerNumber = Customers.customerNumber

in which the '=' joins the relations, for what product does each customer have.

And a relation like:

"Which products does customer number 20 have?"

SELECT DISTINCT Customers.customerName, productName
FROM Products, OrderDetails, Orders, Customers
WHERE Products.productCode = OrderDetails.productCode and
OrderDetails.orderNumber = Orders.orderNumber and
Orders.customerNumber = Customers.customerNumber and
Customers.customerNumber = 20

And from this point I try to figure out how to find out which customers who have ordered all products that customer 20 has ordered.
First I thought maybe if the latter relation (the projected productName) can be tested whether is a subset to each of the groups of the customers products.

I may think it wrong, the last try was:

SELECT Customers.customerName, Products.productName
FROM Products, OrderDetails, Orders, Customers
WHERE Products.productCode = OrderDetails.productCode and
OrderDetails.orderNumber = Orders.orderNumber and
Orders.customerNumber = Customers.customerNumber
and Customers.customerNumber = 20 and
Products.productName in
(SELECT Products.productName
FROM Products, OrderDetails, Orders, Customers
WHERE Products.productCode = OrderDetails.productCode and
OrderDetails.orderNumber = Orders.orderNumber and
Orders.customerNumber = Customers.customerNumber)

J.Saetrum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-15 : 16:29:14

Move the customerNumber=20 into the IN (...). I'd handle this differently by joining to a derived table, but I am not clear on what your instructor is wanting from you. I try to avoid IN at all costs for performance reasons, but it's quite possible your instructor is looking for this as the answer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jsaetrum
Starting Member

7 Posts

Posted - 2011-09-15 : 16:38:12

Thanks for your reply!

Yes, I considered to use IN, it seems logical, but you mean I need to use tuple variables and operate on a derived relation and the original?

These are just basic exercise questions, so it may not be a big deal using IN.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-15 : 16:42:26

IN solves your problem. It's just not the way that I would do it. I am sure your instructor would want you to use IN.

Are you able to test your query to see if it gives the expected result?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jsaetrum
Starting Member

7 Posts

Posted - 2011-09-15 : 16:54:16

Hmm, I get a superset of the right answer. I got this result earlier.

Since it was all the products that 20 has, then I thought each customer had to have a subset which is the set of 20's products.

such that
{products which 20 has} is in {products for each customer}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-15 : 16:55:22

Did you move the 20 to the IN?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jsaetrum
Starting Member

7 Posts

Posted - 2011-09-15 : 17:01:58

Now I tried this:

SELECT DISTINCT Customers.customerName
FROM Products, OrderDetails, Orders, Customers
WHERE Products.productCode = OrderDetails.productCode and
OrderDetails.orderNumber = Orders.orderNumber and
Orders.customerNumber = Customers.customerNumber and
Products.productName in
(SELECT Products.productName
FROM Products, OrderDetails, Orders, Customers
WHERE Products.productCode = OrderDetails.productCode and
OrderDetails.orderNumber = Orders.orderNumber and
Orders.customerNumber = Customers.customerNumber
and Customers.customerNumber = 20 )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-15 : 17:17:28

Could you show us some sample data from the table and then exactly what your code is supposed to return?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources