Re: anding across rows, how

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
Date: 13 Apr 2002 09:59:36 -0700
Message-ID: <6dae7e65.0204130859.355d125c@posting.google.com>

stephen_james_at_uk2.net (Stephen James) 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 ..."

try

select clientid from clients c where exists (

	select 1 from Orders o, Items i where
	c.orderid = o.orderid and
	o.itemid  = i.itemid and 
	i.type = 'CARPET'
) and exists (
	select 1 from Orders o, Items i where
	c.orderid = o.orderid and
	o.itemid  = i.itemid and 
	i.type = 'CHAIR'

) and ...         

> "What Customers have ordered either Carpets and Chairs and ..."

select clientid from clients c where (exists (

	select 1 from Orders o, Items i where
	c.orderid = o.orderid and
	o.itemid  = i.itemid and 
	i.type = 'CARPET'
) or exists (
	select 1 from Orders o, Items i where
	c.orderid = o.orderid and
	o.itemid  = i.itemid and 
	i.type = 'CHAIR'

)) and ...

> "What Customers have ordered an item like 'C%' and ..."

select clientid from clients c where exists (

	select 1 from Orders o, Items i where
	c.orderid = o.orderid and
	o.itemid  = i.itemid and 
	i.type like 'C%'

) and ...

[...]

/Lennart Received on Sat Apr 13 2002 - 11:59:36 CDT