pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: stuart@ludwig.ucl.ac.uk
To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
Cc: pgsql-sql@postgreSQL.org
Cc: stuart@ludwig.ucl.ac.uk
Subject: Re: [SQL] Tricky -to me!- SQL query.
Date: Tue, 12 Jan 1999 13:41:57 +0000
Message-ID: <v01530512b2c0fda4dc0c@[128.40.242.176]> (raw)

>> Stuart wrote:
>>
>> 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)?
>
> Remigiusz answered:
>
>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 ;-)

Hello Rem,

Yes, there is a misunderstanding here, sorry about that, could be my posting.

The first solution would return an empty table because there are no cases
where fruit can be equal to two different things (the data in fruit are
atomic so the field fruit can only ever be equal to one thing).

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.

regards,

Stuart.

PS.  Of course I actually quite like oranges ;)

+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+





view thread (5+ messages)  latest in thread

Message-ID: <v01530512b2c0fda4dc0c@[128.40.242.176]>
Permalink:  ../v01530512b2c0fda4dc0c@%5B128.40.242.176%5D/
Also on:    postgresql.org/message-id/v01530512b2c0fda4dc0c@[128.40.242.176]

 · 

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: stuart@ludwig.ucl.ac.uk, rems@gdansk.sprint.pl
  Subject: Re: [SQL] Tricky -to me!- SQL query.
  In-Reply-To: <v01530512b2c0fda4dc0c@[128.40.242.176]>
  References: <v01530512b2c0fda4dc0c@[128.40.242.176]>

* 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