Re: anding across rows, how

You could use SQL's INTERSECT (for OR) and UNION (for AND) and EXCEPT (for NOT) to combine multiple SELECT queries.

Regards,
Greg Gaughan www.thinksql.co.uk

"Stephen James" <stephen_james_at_uk2.net> wrote in message news: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 - 16:18:10 CEST