Posted by Hugo Kornelis on 04/17/07 11:37

On 16 Apr 2007 21:15:59 -0700, tizmagik@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

[Back to original message]