Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.1/8.9.1) with SMTP id FAA14032 for ; Wed, 13 Jan 1999 05:00:22 -0500 (EST) (envelope-from wieck@sapserv.debis.de) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m100N5M-000EBPC; Wed, 13 Jan 99 10:59 MET Message-Id: From: jwieck@debis.com (Jan Wieck) Subject: Re: [SQL] Tricky -to me!- SQL query. To: stuart@ludwig.ucl.ac.uk (Stuart Rison) Date: Wed, 13 Jan 1999 10:59:35 +0100 (MET) Cc: rems@gdansk.sprint.pl, pgsql-sql@postgreSQL.org, stuart@ludwig.ucl.ac.uk Reply-To: jwieck@debis.com (Jan Wieck) In-Reply-To: from "Stuart Rison" at Jan 12, 99 01:41:57 pm X-Mailer: ELM [version 2.4 PL25] Content-Type: text 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) #