>>>My nephew asked for some help on a query and so far I can't seem to think of a good way to do it. Here is his question.
>>>
>>>A one-to-many relationship where the parent table is a list of customers and the child table is a list of things they've bought. It's easy enough to select customers who have bought a television. It's also simple to select customers who have bought a television or a couch. But how do I select customers who have bought both a television and a couch? The following is returning customers who have bought both or one of the items:
>>>
>>>
>>>I don't have data to test with and I was hoping someone could had an idea.
>>>
>>>Thanks
>>
>>select Cust.CustomerId, Cust.CustomerName
>>from Customer Cust
>>inner join Sales S On Cust.CustomerId = S.CustomerId
>>where S.Product IN ('Product1', 'Product2')
>>GROUP BY Cust.CustomerID, Cust.CustomerName
>>HAVING MIN(S.Product) = 'Product1' and MAX(S.Product) = 'Product2'
>>
>>This is solution for just two products.
>>
>>See more details in this article
>>
>>
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.
Also, did you check the article?
If it's not broken, fix it until it is.
My Blog