pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) #





view thread (5+ messages)

Message-ID: <m100N5M-000EBPC@orion.SAPserv.Hamburg.dsh.de>
Permalink:  ../m100N5M-000EBPC@orion.SAPserv.Hamburg.dsh.de/
Also on:    postgresql.org/message-id/m100N5M-000EBPC@orion.SAPserv.Hamburg.dsh.de

 · 

reply

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