Re: [SQL] Tricky -to me!- SQL query.

>
> Consider the following table:
> dev_brecard=> select * from test order by person;
> person|fruit
> ------+---------
> lucy |mandarins
> lucy |tomatoes
> lucy |pears
> lucy |oranges
> lucy |apples
> peter |pears
> peter |apples
> peter |oranges
> peter |prunes
> robert|figs
> robert|dates
> stuart|apples
> stuart|pears
> stuart|prunes
> stuart|bananas
> stuart|kumquats
> (16 rows)
>
> (code for creating and populating table is in a PS at the end of this posting)
>
> You can assume that the table is appropriately normalised and that there is
> a composite primary key for it (i.e. each COMBINATION of person and fruit
> will appear only once and neither of the fields can be NULL)
>
> How do I select from all person who like 'pears' and 'apples' (in this
> case, lucy, peter and stuart) or 'pears', 'apples' and 'oranges' (in this
> case, lucy and peter)?

What about:
SELECT person FROM test WHERE fruit='pears' AND fruit='apples' AND
fruit='oranges';
or just
SELECT person FROM test WHERE fruit IN ('pears', 'apples','oranges');

For me strange question - so I think I don't mean exactly what you do ;-)
Could You explain for what You wanna this?
Rem

-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems(at)gdansk(dot)sprint(dot)pl * *
-----------------------------------------------------------------*****----------