RE: [GENERAL] Tricky SQL query (tried [SQL])

IMHO the best table layout for your data would be:
DROP TABLE person;
DROP TABLE fruit;
DROP TABLE person_fruit;
CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE fruit (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE person_fruit (p_id INT, f_id INT);
CREATE UNIQUE INDEX pk_person_fruit ON person_fruit (p_id, f_id);
INSERT INTO person (name) VALUES ('lucy');
INSERT INTO person (name) VALUES ('peter');
INSERT INTO person (name) VALUES ('robert');
INSERT INTO person (name) VALUES ('stuart');
INSERT INTO fruit (name) VALUES ('mandarins');
INSERT INTO fruit (name) VALUES ('tomatoes');
INSERT INTO fruit (name) VALUES ('pears');
INSERT INTO fruit (name) VALUES ('oranges');
INSERT INTO fruit (name) VALUES ('apples');
INSERT INTO fruit (name) VALUES ('prunes');
INSERT INTO fruit (name) VALUES ('figs');
INSERT INTO fruit (name) VALUES ('dates');
INSERT INTO fruit (name) VALUES ('bananas');
INSERT INTO fruit (name) VALUES ('kumquats');
INSERT INTO person_fruit (p_id, f_id) VALUES (1,1);
INSERT INTO person_fruit (p_id, f_id) VALUES (1,2);
INSERT INTO person_fruit (p_id, f_id) VALUES (1,3);
INSERT INTO person_fruit (p_id, f_id) VALUES (1,4);
INSERT INTO person_fruit (p_id, f_id) VALUES (1,5);
INSERT INTO person_fruit (p_id, f_id) VALUES (2,3);
INSERT INTO person_fruit (p_id, f_id) VALUES (2,5);
INSERT INTO person_fruit (p_id, f_id) VALUES (2,4);
INSERT INTO person_fruit (p_id, f_id) VALUES (2,6);
INSERT INTO person_fruit (p_id, f_id) VALUES (3,7);
INSERT INTO person_fruit (p_id, f_id) VALUES (3,8);
INSERT INTO person_fruit (p_id, f_id) VALUES (4,5);
INSERT INTO person_fruit (p_id, f_id) VALUES (4,3);
INSERT INTO person_fruit (p_id, f_id) VALUES (4,6);
INSERT INTO person_fruit (p_id, f_id) VALUES (4,9);
INSERT INTO person_fruit (p_id, f_id) VALUES (4,10);
SELECT p.name AS person, f.name AS fruit
FROM person p, fruit f, person_fruit pf
WHERE p.id = pf.p_id AND f.id = pf.f_id
ORDER BY p.name; --your 1st select;

SELECT DISTINCT p.name FROM person p
WHERE EXISTS(SELECT 1
FROM person_fruit pf
WHERE pf.p_id = p.id AND
EXISTS(SELECT 1
FROM fruit f
WHERE f.id = pf.f_id AND
f.name IN ('pears', 'apples',
'oranges'))); --Answer to your actual question

I think that this is 4th(or 5th) Normal Form (never had a DB class :^P).
Hope this helps,
-DEJ

> -----Original Message-----
> From: Dustin Sallings [mailto:dustin(at)spy(dot)net]
> Sent: Wednesday, January 13, 1999 11:48 AM
> To: Stuart Rison
> Cc: pgsql-general(at)postgreSQL(dot)org
> Subject: Re: [GENERAL] Tricky SQL query (tried [SQL])
>
>
> On Wed, 13 Jan 1999, Stuart Rison wrote:
>
> # 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)
>
> # 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)
>
> Actually, it would be normalized a little better if you weren't
> replicating person names and fruit names for every row.
>
> # 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 got this to work:
>
> select distinct person from test
> where likes(person, 'oranges')
> and likes(person, 'apples')
> and likes(person, 'pears')
>
> Where likes is defined as follows:
>
> create function likes(text, text) returns bool as
> '
> declare
> ret bool;
> cnt integer;
> begin
> select count(*) into cnt from test where person = $1
> and fruit = $2;
> if cnt = 0 then
> ret=0;
> else
> ret=1;
> end if;
> return(ret);
> end;
> ' language 'plpgsql';
>
>
> # 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. But does postgreSQL have a INTERSECTION operator?
> #
> # 2) Use nested subselects:
> #
> # 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'
> # )
> # )
> #
> # But how efficient will this be if I start looking for 6 or
> seven fruits in
> # a table with hundreds of entries?
> #
> # 3) Am I storing this sort of data in to wrong kind of form (should I
> # somehow denormalise? if so, how?)?
> #
> # Any suggestions????
> #
> # thanks for any help out there!
> #
> # 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(at)ludwig(dot)ucl(dot)ac(dot)uk |
> # +-------------------------+--------------------------------------+
> #
> #
> #
> #
>
> --
> SA, beyond.com My girlfriend asked me which one I
> like better.
> pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin(at)spy(dot)net>
> | Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65
> 51 98 D8 BE
> L_______________________ I hope the answer won't upset her.
> ____________
>
>