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

Stuart Rison wrote:

>
> >> Stuart wrote:
> >>
> >> Consider the following table:
> >> dev_brecard=> select * from test order by person;
> >> person|fruit
> >> ------+---------
> >> lucy |mandarins
> >> [...]
> >>
> >> 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)?
> >
> > Remigiusz answered:
> >
> >What about:
> >[...]
>
> The second of solutions answers the question "Who eats pears or eats apples
> or eats oranges?" but not the question "Who eats pears AND apples AND
> oranges?" (i.e. it would give the answers lucy, peter and stuart when the
> actual answers should be lucy and peter because, in the example table,
> stuart does not eat oranges).
>
> >Could You explain for what You wanna this?
>
> Hope this is clearer.

This one works:

pgsql=> SELECT DISTINCT person FROM test t1
pgsql-> WHERE 3 = (SELECT count(*) FROM test t2
pgsql-> WHERE t2.person = t1.person
pgsql-> AND t2.fruit IN ('pears', 'apples', 'oranges'));
person
------
lucy
peter
(2 rows)

pgsql=> SELECT DISTINCT person FROM test t1
pgsql-> WHERE 2 = (SELECT count(*) FROM test t2
pgsql-> WHERE t2.person = t1.person
pgsql-> AND t2.fruit IN ('pears', 'apples'));
person
------
lucy
peter
stuart
(3 rows)

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #