Thread: [SQL] Tricky -to me!- SQL query.
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 |
+-------------------------+--------------------------------------+
>
> 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)?
What about:
SELECT person FROM test WHERE fruit='pears' AND fruit='apples' AND
fruit='oranges';
or just
SELECT person FROM test WHERE fruit IN ('pears', 'apples','oranges');
For me strange question - so I think I don't mean exactly what you do ;-)
Could You explain for what You wanna this?
Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------
>> Stuart wrote:
>>
>> 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)?
>
> Remigiusz answered:
>
>What about:
>SELECT person FROM test WHERE fruit='pears' AND fruit='apples' AND
>fruit='oranges';
>or just
>SELECT person FROM test WHERE fruit IN ('pears', 'apples','oranges');
>
>For me strange question - so I think I don't mean exactly what you do ;-)
Hello Rem,
Yes, there is a misunderstanding here, sorry about that, could be my posting.
The first solution would return an empty table because there are no cases
where fruit can be equal to two different things (the data in fruit are
atomic so the field fruit can only ever be equal to one thing).
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.
regards,
Stuart.
PS. Of course I actually quite like oranges ;)
+-------------------------+--------------------------------------+
| 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 |
+-------------------------+--------------------------------------+
> >
> >What about:
> >SELECT person FROM test WHERE fruit='pears' AND fruit='apples' AND
> >fruit='oranges';
> >or just
> >SELECT person FROM test WHERE fruit IN ('pears', 'apples','oranges');
> >
> >For me strange question - so I think I don't mean exactly what you do ;-)
>
> Hello Rem,
>
> Yes, there is a misunderstanding here, sorry about that, could be my posting.
> The first solution would return an empty table because there are no cases
> where fruit can be equal to two different things (the data in fruit are
> atomic so the field fruit can only ever be equal to one thing).
>
> 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).
>
Oh, no... I apologize for that - is here to hot or smth.
I must be ill - when I wrote fruit='smth' and fruit='smth_else' - it
couldn't be solution for that.
You're absolutely right.
It needs nested queries or function(which also should realize some
queries)
Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------
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) #