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@debis.com (Jan Wieck) #