pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: stuart@ludwig.ucl.ac.uk
To: pgsql-sql@postgresql.org
Cc: stuart@ludwig.ucl.ac.uk
Subject: [SQL] Tricky -to me!- SQL query.
Date: Tue, 12 Jan 1999 11:17:54 +0000
Message-ID: <v0153050fb2c0d959552d@[128.40.242.176]> (raw)

Dear All,

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)?

I re-read my SQL books but I am still somewhat stumped.  Things I could
think of for that sort of query:

1) Select all persons who like 'pears'; Select all persons who like
'apples'; Select all persons who like 'oranges'; Calculate the INTERSECTION
of these sets (this sort of operation appears to use the EXISTS operator?)

2) Use nested subselects:

Select person from test where person in (
        Select person from test where fruit='pears' and person in (
                Select person from test where fruit='apples' and person in (
                        Select person from test where fruit='oranges'
                )
        )
)

What way do you suggest????

Also, am I storing this sort of data in to wrong kind of form (should I
somehow denormalise?  if so, how?)?

Could you please cc your answers to: stuart@ludwig.ucl.ac.uk

thanks for any help out there!

regards,

Stuart.

PS.  Code to cut and paste for table:

create table test (person    varchar(25), fruit     varchar(25));
insert into test values ('stuart','apples');
insert into test values ('stuart','pears');
insert into test values ('stuart','bananas');
insert into test values ('stuart','kumquats');
insert into test values ('peter','oranges');
insert into test values ('peter','prunes');
insert into test values ('lucy','mandarins');
insert into test values ('lucy','tomatoes');
insert into test values ('peter','apples');
insert into test values ('lucy','apples');
insert into test values ('peter','pears');
insert into test values ('lucy','pears');
insert into test values ('lucy','oranges');
insert into test values ('stuart','prunes');
insert into test values ('robert','figs');
insert into test values ('robert','dates');

+-------------------------+--------------------------------------+
| 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: <v0153050fb2c0d959552d@[128.40.242.176]>
Permalink:  ../v0153050fb2c0d959552d@%5B128.40.242.176%5D/
Also on:    postgresql.org/message-id/v0153050fb2c0d959552d@[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
  Subject: Re: [SQL] Tricky -to me!- SQL query.
  In-Reply-To: <v0153050fb2c0d959552d@[128.40.242.176]>
  References: <v0153050fb2c0d959552d@[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