Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22]) by hub.org (8.9.1/8.9.1) with ESMTP id GAA22361 for ; Tue, 12 Jan 1999 06:09:02 -0500 (EST) (envelope-from stuart@ludwig.ucl.ac.uk) Received: from kestrel.ludwig.ucl.ac.uk by mail-b.bcc.ac.uk with SMTP (XT-PP); Tue, 12 Jan 1999 11:08:54 +0000 Received: from [128.40.242.176] by kestrel.ludwig.ucl.ac.uk (SMI-8.6/SMI-SVR4) id LAA08837; Tue, 12 Jan 1999 11:10:47 GMT X-Sender: stuart@kestrel.ludwig.ucl.ac.uk Message-Id: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Tue, 12 Jan 1999 11:17:54 +0000 To: pgsql-sql@postgresql.org From: stuart@ludwig.ucl.ac.uk (Stuart Rison) Subject: [SQL] Tricky -to me!- SQL query. Cc: stuart@ludwig.ucl.ac.uk 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 | +-------------------------+--------------------------------------+