Need help writing a query

[tizmagik]

Database consists of the following 4 tables with respective
attributes:

CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#]
ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#]
BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE]
PREFER(I#, C#), the key is [I#, C#]

I'm trying to construct the following query (in SQL)

List of customers that bought all the items that John prefers.

I can get the list of all the items that John prefers, but I'm not
sure how to check that list against customers who bought ALL those
items. I'm assuming it's either a division or some sort of subtraction
but I'm not sure how to formulate the SQL query.

Any and all help is appreciated, thanks!

[Hugo Kornelis]

On 16 Apr 2007 21:15:59 -0700, ti******@gmail. com wrote:

Perhaps
there is a solution without using COUNT, I will be sure to ask the
professor during next lecture.

Hi tizmagik,

Indeed, there is. It is called "inverse logic". If a customer has bought
every item John prefers, than clearly, there can not be any single item
that is preferred by John but that the customer didn't buy. I'm sure
that you're able to cough up the actual query for that logic. :-)

This solution is actually the solution most people produce first for
this problem. Maybe because many classes explain subqueries and NOT
EXISTS before moving on to aggregates and HAVING? Or maybe it's just
related to how our brain functions? Anyway, the version as posted by Ed
looks like (I didn't check in detail) the second standard solution to
this problem, based on the logic "if a customer buys everything John
prefers, then the number of items bought by the customers *and* prefered
by John must be equal to the number of items prefered by John. Outside
of class, you'd probably try both against the actual data on the actual
database to figure out which one gives the best performance.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Erland Sommarskog]

(ti******@gmail .com) writes:

I am fairly new to SQL programming and believe me that wasn't the only
thing that the assignment asked, however this question was the one
question that I had a lot of trouble with and the lack of a book for
the class (it's strictly lecture notes) was what brought me to look
for help elsewhere.

If I would have based the SQL query on the examples given by the
professor I would have gotten a list of all customers who bought *at
least one* item that "john" prefers, as apposed to the correct list
(all customers who bought *all* the items that john prefers).
Furthermore, the professor did not go over COUNT so I really did not
see any way of doing it with what he has gone over so far. Perhaps
there is a solution without using COUNT, I will be sure to ask the
professor during next lecture.

So that's another problem with asking for help with class assignments
on Usenet. While the COUNT may be a more elegant solution, the professor
probably wanted you to exercise in the use of EXISTS and NOT EXISTS.
Which certainly is an investment worth making, because such problem
as commonplace. (While the exercise you had, has a disctinct flavoour
of class assignment. Did I ever encounter such a problem myself? I can't
recall any.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[tizmagik]

Thanks for your input guys.

I'll be sure to post here when the solution without using COUNT is
covered (assuming there is one).

[tizmagik]

Okay, this is what I have come up with so far:

SELECT c1.Customer_Nam e
FROM Customer c1
WHERE c1.CustomerID IN (
SELECT B.CustomerID
FROM Bought B
WHERE B.ItemID IN (
SELECT P.ItemID
FROM Prefer P, Customer c2
WHERE c2.Customer_Nam e = 'John'
AND c2.CustomerID = P.CustomerID ) )

But that brings me back to the problem where it will list customers
that bought *at least one* of the items that John prefers, not ALL of
the items that John prefers, that query gives:
John
Jeremy
Michelle

The expected answer is just 'Michelle' as being the only customer that
bought ALL of the items that John prefers with the following data:

- CUSTOMER table
CREATE TABLE Customer (
CustomerID int(4) NOT NULL,
Customer_Name varchar(30) NOT NULL,
Address varchar(30) NOT NULL,
PRIMARY KEY (CustomerID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Customer VALUES (1000, 'John', '123 John St.');
INSERT INTO Customer VALUES (1001, 'Jeremy', '456 Jeremy Ave.');
INSERT INTO Customer VALUES (1002, 'Michelle', '789 Michelle Blvd.');
INSERT INTO Customer VALUES (1003, 'Laura', '1011 Laura Way');
INSERT INTO Customer VALUES (1004, 'Nicholas', '1004 Nicholas Place');
INSERT INTO Customer VALUES (1005, 'James', '1005 James Drive');

- ITEM table
CREATE TABLE Item (
ItemID int(11) NOT NULL,
Item_Name varchar(30) NOT NULL,
Manufacturer varchar(30) NOT NULL,
`Year` int(4) NOT NULL,
PRIMARY KEY (ItemID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Item VALUES (9000, 'Camera', 'Nikkon', 1997);
INSERT INTO Item VALUES (9001, 'Camera', 'Sony', 1998);
INSERT INTO Item VALUES (9002, 'Camera', 'Olympus', 2001);
INSERT INTO Item VALUES (9003, 'Camera', 'Olympus', 2001);
INSERT INTO Item VALUES (9004, 'Camera', 'Polaroid', 1991);
INSERT INTO Item VALUES (9005, 'Laptop', 'Dell', 2006);
INSERT INTO Item VALUES (9006, 'Laptop', 'HP', 2005);
INSERT INTO Item VALUES (9007, 'Desktop', 'Dell', 2002);
INSERT INTO Item VALUES (9008, 'Desktop', 'Apple', 2004);
INSERT INTO Item VALUES (9009, 'PDA', 'Palm', 2003);
INSERT INTO Item VALUES (9010, 'PDA', 'Handspring', 1998);
INSERT INTO Item VALUES (9011, 'HDTV', 'Sony', 2004);
INSERT INTO Item VALUES (9012, 'HDTV', 'Samsung', 2005);
INSERT INTO Item VALUES (9013, 'HDTV', 'Toshiba', 2003);
INSERT INTO Item VALUES (9014, 'HDTV', 'Mitsubishi', 2003);
- BOUGHT table
CREATE TABLE Bought (
CustomerID int(4) NOT NULL,
ItemID int(4) NOT NULL,
`Date` date NOT NULL,
Quantity int(5) NOT NULL,
PRIMARY KEY (CustomerID,Ite mID,`Date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Bought VALUES (1002, 9000, '2007-04-01', 5);
INSERT INTO Bought VALUES (1002, 9001, '2007-04-30', 2);
INSERT INTO Bought VALUES (1002, 9008, '2007-04-09', 1);
INSERT INTO Bought VALUES (1002, 9014, '2007-04-15', 1);
INSERT INTO Bought VALUES (1001, 9001, '2007-04-16', 1);
INSERT INTO Bought VALUES (1001, 9008, '2007-04-16', 1);
INSERT INTO Bought VALUES (1000, 9008, '2007-04-16', 5);
INSERT INTO Bought VALUES (1000, 9001, '2007-04-17', 2);
INSERT INTO Bought VALUES (1005, 9003, '2007-04-16', 2);
INSERT INTO Bought VALUES (1004, 9002, '2007-04-16', 1);
INSERT INTO Bought VALUES (1001, 9011, '2007-02-16', 3);
INSERT INTO Bought VALUES (1001, 9010, '2007-02-16', 3);
INSERT INTO Bought VALUES (1003, 9012, '2007-02-16', 1);
INSERT INTO Bought VALUES (1005, 9013, '2007-02-16', 2);
INSERT INTO Bought VALUES (1004, 9006, '2007-04-01', 1);

- PREFER table
CREATE TABLE Prefer (
ItemID int(4) NOT NULL,
CustomerID int(4) NOT NULL,
PRIMARY KEY (ItemID,Custome rID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Prefer VALUES (9000, 1000);
INSERT INTO Prefer VALUES (9001, 1000);
INSERT INTO Prefer VALUES (9002, 1004);
INSERT INTO Prefer VALUES (9003, 1003);
INSERT INTO Prefer VALUES (9006, 1001);
INSERT INTO Prefer VALUES (9007, 1004);
INSERT INTO Prefer VALUES (9007, 1005);
INSERT INTO Prefer VALUES (9008, 1000);
INSERT INTO Prefer VALUES (9008, 1002);
INSERT INTO Prefer VALUES (9008, 1004);
INSERT INTO Prefer VALUES (9009, 1002);
INSERT INTO Prefer VALUES (9013, 1005);
INSERT INTO Prefer VALUES (9014, 1000);
Again, any help is appreciated. (Yes, the professor didn't go over
AutoNumber fields yet in case you're wondering :) )

[tizmagik]

A little closer... I believe this would be the right SQL
theoretically, but this will only work in an Oracle DB or DB that
supports the MINUS operation (MySQL doesnt), so I will try to
reformulate this without using MINUS. I'm guessing it's some sort of
JOIN operation where you check for Nulls and select those that are not
Null...

SELECT B1.CustomerID
FROM Bought B1
WHERE NOT EXISTS (
( SELECT Prefer.ItemID
FROM Prefer, Customer
WHERE Customer.Custom er_Name = 'John'
AND Prefer.Customer ID = Customer.Custom erID
) MINUS (
SELECT B2.ItemID
FROM Bought B2
WHERE B2.CustomerID = B1.CustomerID )
)

Any help would be appreciated.

[Ed Murphy]

ti******@gmail. com wrote:

Okay, this is what I have come up with so far:

SELECT c1.Customer_Nam e
FROM Customer c1
WHERE c1.CustomerID IN (
SELECT B.CustomerID
FROM Bought B
WHERE B.ItemID IN (
SELECT P.ItemID
FROM Prefer P, Customer c2
WHERE c2.Customer_Nam e = 'John'
AND c2.CustomerID = P.CustomerID ) )

But that brings me back to the problem where it will list customers
that bought *at least one* of the items that John prefers, not ALL of
the items that John prefers, that query gives:

I think this will work:

SELECT c1.Customer_Nam e
FROM Customer c1
WHERE 0 = (
SELECT COUNT(*)
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Nam e = 'John'
AND B.CustomerID IS NULL
)

but I still think the positive approach (COUNT = COUNT) is a lot
easier to understand.

[tizmagik]

On Apr 17, 9:43 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

tizma...@gmail. com wrote:

Okay, this is what I have come up with so far:

SELECT c1.Customer_Nam e
FROM Customer c1
WHERE c1.CustomerID IN (
SELECT B.CustomerID
FROM Bought B
WHERE B.ItemID IN (
SELECT P.ItemID
FROM Prefer P, Customer c2
WHERE c2.Customer_Nam e = 'John'
AND c2.CustomerID = P.CustomerID ) )

But that brings me back to the problem where it will list customers
that bought *at least one* of the items that John prefers, not ALL of
the items that John prefers, that query gives:

I think this will work:

SELECT c1.Customer_Nam e
FROM Customer c1
WHERE 0 = (
SELECT COUNT(*)
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Nam e = 'John'
AND B.CustomerID IS NULL
)

but I still think the positive approach (COUNT = COUNT) is a lot
easier to understand.

Thanks Ed, but I'm trying to avoid using COUNT since that was not
covered in class.

[Ed Murphy]

ti******@gmail. com wrote:

On Apr 17, 9:43 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

>tizma...@gmail .com wrote:

>>Okay, this is what I have come up with so far:
SELECT c1.Customer_Nam e
FROM Customer c1
WHERE c1.CustomerID IN (
SELECT B.CustomerID
FROM Bought B
WHERE B.ItemID IN (
SELECT P.ItemID
FROM Prefer P, Customer c2
WHERE c2.Customer_Nam e = 'John'
AND c2.CustomerID = P.CustomerID ) )
But that brings me back to the problem where it will list customers
that bought *at least one* of the items that John prefers, not ALL of
the items that John prefers, that query gives:

I think this will work:

SELECT c1.Customer_Nam e
FROM Customer c1
WHERE 0 = (
SELECT COUNT(*)
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Nam e = 'John'
AND B.CustomerID IS NULL
)

but I still think the positive approach (COUNT = COUNT) is a lot
easier to understand.

Thanks Ed, but I'm trying to avoid using COUNT since that was not
covered in class.

SELECT C1.Customer_Nam e
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Nam e = 'John'
AND B.CustomerID IS NULL
)

or

SELECT C1.Customer_Nam e
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
WHERE C2.Customer_Nam e = 'John'
AND P.ItemID NOT IN (
SELECT B.ItemID
FROM Bought B
WHERE B.CustomerID = C1.CustomerID
)
)

[tizmagik]

On Apr 17, 10:03 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

tizma...@gmail. com wrote:

On Apr 17, 9:43 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

tizma...@gmail. com wrote:
Okay, this is what I have come up with so far:
SELECT c1.Customer_Nam e
FROM Customer c1
WHERE c1.CustomerID IN (
SELECT B.CustomerID
FROM Bought B
WHERE B.ItemID IN (
SELECT P.ItemID
FROM Prefer P, Customer c2
WHERE c2.Customer_Nam e = 'John'
AND c2.CustomerID = P.CustomerID ) )
But that brings me back to the problem where it will list customers
that bought *at least one* of the items that John prefers, not ALL of
the items that John prefers, that query gives:
I think this will work:

SELECT c1.Customer_Nam e
FROM Customer c1
WHERE 0 = (
SELECT COUNT(*)
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Nam e = 'John'
AND B.CustomerID IS NULL
)

but I still think the positive approach (COUNT = COUNT) is a lot
easier to understand.

Thanks Ed, but I'm trying to avoid using COUNT since that was not
covered in class.

SELECT C1.Customer_Nam e
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Nam e = 'John'
AND B.CustomerID IS NULL
)

or

SELECT C1.Customer_Nam e
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
WHERE C2.Customer_Nam e = 'John'
AND P.ItemID NOT IN (
SELECT B.ItemID
FROM Bought B
WHERE B.CustomerID = C1.CustomerID
)
)

That first one didn't work for me, some syntax error, not sure why,
might just be a phpMyAdmin problem, but that second one worked
beautifully.

I'm trying to step through it and understand it line by line now...
this is what I understand from it:

You are selecting all the customers that are not in the following:
- You are selecting all the Items that john prefers, from the list of
items that are not in the list of items that customers have bought

haha really confusing, but I think I get it. Thanks so much for your
help.

[Ed Murphy]

ti******@gmail. com wrote:

That first one didn't work for me, some syntax error, not sure why,
might just be a phpMyAdmin problem,

You do realize this isn't a MySQL group?

but that second one worked beautifully.

I'm trying to step through it and understand it line by line now...
this is what I understand from it:

You are selecting all the customers that are not in the following:
- You are selecting all the Items that john prefers, from the list of
items that are not in the list of items that customers have bought

For each customer, you're looking for items that John prefers
but the customer didn't buy; if there is no such item, then the
customer gets selected.