anding across rows, how

From: Stephen James <stephen_james_at_uk2.net>
Date: 13 Apr 2002 02:46:56 -0700
Message-ID: <dfd51415.0204130146.76b415cf_at_posting.google.com>

Given the data below how would I solve the following questions in SQL
"What Customers have ordered both Carpets and Chairs and ..."
"What Customers have ordered either Carpets and Chairs and ..."
"What Customers have ordered an item like 'C%' and ..."

Clients
++++++
ClientID | OrderID | ...
---------+---------+
1 | 10

Orders
++++++
OrderID | ItemID | ...
--------+--------+

10      | 110
10      | 120
10      | 130

Items
+++++
ItemID | Type | ...
-------+------------+

110    | "CARPET"
120    | "CHAIR"
130    | "DESK"

Given that I'm not sure how long the list of items to match across rows is what the best approach.

* divided and conquer ?.
* build sql on the fly ?.
* do some quite marvellous SQL suggested by the world at large ?.

I've a solution using a temp table and a cursor on a table holding the selection criteria but it's pig ugly and can't help but feel there is a
better way.

Thanks in advance for any suggestions. Received on Sat Apr 13 2002 - 11:46:56 CEST