>>>http://social.technet.microsoft.com/wiki/contents/articles/22165.t-sql-relational-division.aspx
>>
>>I'm not so impressed with those solutions. Why not keep it simple?
>>
>>

>>SELECT * FROM Customer
>>WHERE NOT EXISTS(
>>        SELECT 1 
>>        FROM RequiredItems I 
>>                 LEFT JOIN Sales S ON S.itemid = I.itemid AND S.Cust_id = Customer.Cust_id
>>	WHERE S.Pk IS NULL)
>>

>>
>>There trick is that with the left join, you identify missing items and only selecting the record with the WHERE S.Pk IS NULL condition.
>>
>>And exact division would require an extra NOT EXISTS() to check whether the customer did buy something out of the selection.
>
>Did you test that this solution perform better than the other one which is much more intuitive? I would think group by and having should perform better.

I'm not sure which specific solution you're talking about. The query above performs fine with a queryplan that is pretty simple as long as you've got the right indexes (See attachment). The performance is going to depend on a number of factors, it might be faster in some cases, it might be slower in others. It is going to depend on the distribution of the values. If the sales table is quite small compared to the number of customers the group by approach is going to be faster. However if that is the case, you can tune the query above for performance by adding a EXISTS() clause to check whether at least one sales record for a selected item exists for that customer, avoiding each customer going through the NOT EXISTS() clause if there isn't at least one match.
But as it is, getting through a million record sales table within a second, I'd call acceptable for a start.

Also, IMO, the query above is pretty simple (again see attechment), as long as you'd get the trick of the left join and just filtering out to get the records that do not have a match (kind of equivalent to NOT EXISTS(), but still having access to the values of the left table in the join). Having this one in my evil bag of tricks for many years, it comes natural to me to solve a lot of problems that are more difficult to solve otherwise.

OTOH, you could also do it with:

SELECT * FROM Customer
WHERE (SELECT Count(Distinct S.ItemID) FROM RequiredItems I INNER JOIN Sales S ON S.itemid = I.itemid AND S.Cust_id = Customer.Cust_id) =
    (SELECT Count(*) FROM RequiredItems)

However the execution plan is a but more complicated
>Also, did you check the article?

I read the article and looked at the SQL statements superficially without testing it, but seemed more complex than necessary.
If you were referring to the NAOMI / CELKO solution, you're doing the EXACT DIVISION, which was (AFAIK) not the requirement here, and btw might be better served with an addition to my original query.

SELECT * FROM Customer
WHERE NOT EXISTS(
        SELECT 1  FROM RequiredItems I 
                 LEFT JOIN Sales S ON S.itemid = I.itemid AND S.Cust_id = Customer.Cust_id
	WHERE S.Pk IS NULL)
        AND NOT EXISTS(SELECT 1 FROM Sales WHERE  Cust_id = Customer.Cust_id AND ItemId NOT IN (SELECT ItemId FROM RequiredItems))

Without the last line, its a normal DIVISION, with it, and EXACT DIVISION. This simplicity IMO is making it a better solution. You could do the same with the alternative SQL above.

Walter,