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!(tizmagik@.gmail.com) writes:
Quote: Originally Posted by 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. |
This smells of class assignment, but OK, let's go for it anyway.
If memory serves this is something they for some reason I've never
understood call relational division. In less occluded terms, a HAVING
clause can shortcut the need for a couple of EXISTS and NOT EXISTS.
SELKCT C.C#, C.CUSTOMER_NAME
FROM CUSTOMER C
JOIN (SELECT B.C#
FROM BOUGHT B
GROUP BY B.C#
HAVING COUNT(DISTINCT B.I#) =
(SELECT COUNT(*)
FROM PREFER P
JOIN CUSTOMER C ON P.C# = C.C#
WHERE C.CUSTOMER_NAME = 'John')) AS res
ON C.C# = res.C#
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
Quote: Originally Posted by (tizmagik@.gmail.com) writes: |
Quote: Originally Posted by >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. |
>
This smells of class assignment, but OK, let's go for it anyway.
>
If memory serves this is something they for some reason I've never
understood call relational division. In less occluded terms, a HAVING
clause can shortcut the need for a couple of EXISTS and NOT EXISTS.
>
SELKCT C.C#, C.CUSTOMER_NAME
FROM CUSTOMER C
JOIN (SELECT B.C#
FROM BOUGHT B
GROUP BY B.C#
HAVING COUNT(DISTINCT B.I#) =
(SELECT COUNT(*)
FROM PREFER P
JOIN CUSTOMER C ON P.C# = C.C#
WHERE C.CUSTOMER_NAME = 'John')) AS res
ON C.C# = res.C#
That will select all customers who bought the same /number/ of
items as what John prefers, but not necessarily the same items.
I think this will select all customers who bought all the items
that John prefers:
SELECT C.C#, C.CUSTOMER_NAME
FROM CUSTOMER C
JOIN BOUGHT B ON C.C# = B.C#
JOIN PREFER P ON B.I# = P.I#
JOIN CUSTOMER J ON P.C# = J.C# AND J.CUSTOMER_NAME = 'John'
GROUP BY C.C#, C.CUSTOMER_NAME
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM PREFER P
JOIN CUSTOMER J ON P.C# = J.C# AND J.CUSTOMER_NAME = 'John'
)|||Erland: Why would it matter if it's a class assignment or not? Is not
the purpose of a Usenet group to share and learn from each other? What
relevance is it what the knowledge will be used for? Thank you for
your attempt anyway, but Ed's answer seems more in line with what the
query is intended to do.
Thank you Ed, seems to be what I'm looking for, it's interesting, I
never even though of setting up a Count, but now that I look at it,
it's hard to imagine any other way of doing it.
Thanks again :)|||tizmagik@.gmail.com wrote:
Quote: Originally Posted by Thank you Ed, seems to be what I'm looking for, it's interesting, I
never even though of setting up a Count, but now that I look at it,
it's hard to imagine any other way of doing it. |
I thought of doing J JOIN P LEFT JOIN B and looking for nulls, but I
can't figure out a way to do it, and even if there is one, it would
probably be less clear than the COUNT = COUNT method.|||<tizmagik@.gmail.comwrote in message
news:1176697766.757819.271590@.b75g2000hsg.googlegr oups.com...
Quote: Originally Posted by Erland: Why would it matter if it's a class assignment or not? |
It matters if you're asking others to do your homework. Some professors
frown upon that. (and it could, in some cases, be considered a form of
cheating.)
Quote: Originally Posted by Is not
the purpose of a Usenet group to share and learn from each other? |
Oh certainly. And I think Erland would agree, many of us here love to help
others (and certainly to learn from others). But from time to time (and I'm
not claiming you're one of them) who come here looking simply for answers to
homework problems, not necessarily understanding. That benefits no one in
the long run.
Quote: Originally Posted by What
relevance is it what the knowledge will be used for? Thank you for
your attempt anyway, but Ed's answer seems more in line with what the
query is intended to do.
>
Thank you Ed, seems to be what I'm looking for, it's interesting, I
never even though of setting up a Count, but now that I look at it,
it's hard to imagine any other way of doing it.
>
Thanks again :)
> |
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>Why would it matter if it's a class assignment or not? Is not the purpose of a Usenet group to share and learn from each other? <<
In most university systems having someone else do your homework gets
you kicked out of school. It is academic fraud. I know. I have had
two kids expelled from schools in New Zealand and Australia for doing
this. An old friend of mine got a "social engineer" taken out of
Georgia Tech; etc.|||--CELKO-- wrote:
Quote: Originally Posted by |
Quote: Originally Posted by |
Quote: Originally Posted by >>Why would it matter if it's a class assignment or not? Is not the purpose of a Usenet group to share and learn from each other? << |
>
In most university systems having someone else do your homework gets
you kicked out of school. It is academic fraud. I know. I have had
two kids expelled from schools in New Zealand and Australia for doing
this. An old friend of mine got a "social engineer" taken out of
Georgia Tech; etc.
Same rule applies here at the University of Washington.
Get caught cheating and it is a one-way trip.
Anyone that thinks instructors such as myself are not watching
these groups is in the wrong business.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||(tizmagik@.gmail.com) writes:
Quote: Originally Posted by Erland: Why would it matter if it's a class assignment or not? Is not
the purpose of a Usenet group to share and learn from each other? |
But Usenet is not the best place to learn everything. If you have some
experience in the field of SQL programming, I can assume that you can
understand the solution I post to some extend and learn from it.
But if you are a student who is not interested in doing his homework?
I remember way back when, when I was a student myself, and also worked as
an assistant teacher in programming. Back in those days, the assignments
were made on paper, and when the student was approved for this week's
exercise I would give him a paper with the "ideal" solution. Sometimes
it happened that students arrived to the classroom with this ideal
solution, in which case I told them not do to it again. And I did not
approve them for that assignment. (It was permitted to miss one or two.)
One year I had a group in Programming 2, an optional class which taught
programming structures. I had one guy who consistently arrived with
the ideal solution, and I knew that his girlfriend was taking the same
class. I figured that at this stage, he should know better than cheating,
so I did not say anything. I approved his "solutions" without a comment
and let him go. But these assignments were not all - there was a written
exam as well. And when the results came up, his girl-friend was there.
But, not surprisingly, he wasn't. He had just copied the ideal solutions,
but he hadn't learnt anything.
Quote: Originally Posted by Thank you Ed, seems to be what I'm looking for, it's interesting, I
never even though of setting up a Count, but now that I look at it,
it's hard to imagine any other way of doing it. |
Sorry for the incorrect solution, but there is a standard recommendation
for this type of questions, and that is that you post:
o CREATE TABLE statements for your table(s).
o INSERT statements with sample data.
o The desireed output, given the sample.
That makes it easy to copy and paste to develop a tested solution. Without
that, most people here tend to just type something up, and sometimes
there are errors.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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.
Thanks for those that helped.|||On 16 Apr 2007 21:15:59 -0700, tizmagik@.gmail.com wrote:
Quote: Originally Posted by 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|||(tizmagik@.gmail.com) writes:
Quote: Originally Posted by 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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your input guys.
I'll be sure to post here when the solution without using COUNT is
covered (assuming there is one).|||Okay, this is what I have come up with so far:
SELECT c1.Customer_Name
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_Name = '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,ItemID,`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,CustomerID)
) 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 :) )|||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.Customer_Name = 'John'
AND Prefer.CustomerID = Customer.CustomerID
) MINUS (
SELECT B2.ItemID
FROM Bought B2
WHERE B2.CustomerID = B1.CustomerID )
)
Any help would be appreciated.|||tizmagik@.gmail.com wrote:
Quote: Originally Posted by Okay, this is what I have come up with so far:
>
SELECT c1.Customer_Name
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_Name = '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_Name
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_Name = 'John'
AND B.CustomerID IS NULL
)
but I still think the positive approach (COUNT = COUNT) is a lot
easier to understand.|||On Apr 17, 9:43 pm, Ed Murphy <emurph...@.socal.rr.comwrote:
Quote: Originally Posted by tizma...@.gmail.com wrote: |
Quote: Originally Posted by Okay, this is what I have come up with so far: |
>
Quote: Originally Posted by SELECT c1.Customer_Name
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_Name = 'John'
AND c2.CustomerID = P.CustomerID ) ) |
>
Quote: Originally Posted by 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_Name
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_Name = '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.|||tizmagik@.gmail.com wrote:
Quote: Originally Posted by On Apr 17, 9:43 pm, Ed Murphy <emurph...@.socal.rr.comwrote: |
Quote: Originally Posted by >tizma...@.gmail.com wrote: |
Quote: Originally Posted by >>Okay, this is what I have come up with so far:
>>SELECT c1.Customer_Name
>>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_Name = '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_Name
>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_Name = '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_Name
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_Name = 'John'
AND B.CustomerID IS NULL
)
or
SELECT C1.Customer_Name
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
WHERE C2.Customer_Name = 'John'
AND P.ItemID NOT IN (
SELECT B.ItemID
FROM Bought B
WHERE B.CustomerID = C1.CustomerID
)
)|||On Apr 17, 10:03 pm, Ed Murphy <emurph...@.socal.rr.comwrote:
Quote: Originally Posted by tizma...@.gmail.com wrote: |
Quote: Originally Posted by On Apr 17, 9:43 pm, Ed Murphy <emurph...@.socal.rr.comwrote: |
Quote: Originally Posted by tizma...@.gmail.com wrote:
>Okay, this is what I have come up with so far:
>SELECT c1.Customer_Name
>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_Name = '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: |
>
Quote: Originally Posted by |
Quote: Originally Posted by SELECT c1.Customer_Name
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_Name = 'John'
AND B.CustomerID IS NULL
) |
>
Quote: Originally Posted by |
Quote: Originally Posted by but I still think the positive approach (COUNT = COUNT) is a lot
easier to understand. |
>
Quote: Originally Posted by Thanks Ed, but I'm trying to avoid using COUNT since that was not
covered in class. |
>
SELECT C1.Customer_Name
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_Name = 'John'
AND B.CustomerID IS NULL
)
>
or
>
SELECT C1.Customer_Name
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
WHERE C2.Customer_Name = '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.|||tizmagik@.gmail.com wrote:
Quote: Originally Posted by 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?
Quote: Originally Posted by 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.|||I couldn't find a suitable MySQL (or just SQL group) that was as
active as this one. Thanks though.|||(tizmagik@.gmail.com) writes:
Quote: Originally Posted by I couldn't find a suitable MySQL (or just SQL group) that was as
active as this one. Thanks though. |
There is a comp.databases.mysql. Don't how much traffic there is though.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
. Given this insert statement (which is being generated from a