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. ____________