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!

[Erland Sommarskog]

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

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

[Ed Murphy]

Erland Sommarskog wrote:

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

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

[tizmagik]

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

[Ed Murphy]

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

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.

[Greg D. Moore \(Strider\)]

<ti******@gmail .comwrote in message
news:11******** **************@ b75g2000hsg.goo glegroups.com.. .

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

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.

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

[--CELKO--]

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

[DA Morgan]

--CELKO-- wrote:

>>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
da******@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Erland Sommarskog]

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

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.

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, 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]

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.