From: jwieck@debis.com
To: stuart@ludwig.ucl.ac.uk
Cc: rems@gdansk.sprint.pl
Cc: pgsql-sql@postgreSQL.org
Cc: stuart@ludwig.ucl.ac.uk
Subject: Re: [SQL] Tricky -to me!- SQL query.
Date: Wed, 13 Jan 1999 10:59:35 +0100 (MET)
Message-ID: <m100N5M-000EBPC@orion.SAPserv.Hamburg.dsh.de> (raw)
In-Reply-To: <v01530512b2c0fda4dc0c@[128.40.242.176]>
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) #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jwieck@debis.com, stuart@ludwig.ucl.ac.uk, rems@gdansk.sprint.pl
Subject: Re: [SQL] Tricky -to me!- SQL query.
In-Reply-To: <m100N5M-000EBPC@orion.SAPserv.Hamburg.dsh.de>
References: <v01530512b2c0fda4dc0c@[128.40.242.176]> <m100N5M-000EBPC@orion.SAPserv.Hamburg.dsh.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox