List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 21 1999 10:33am
Subject:Re: SQL problem
View as plain text  
On Tue, 1999-09-21 07:39:22 +0200, Maarten Roosendaal wrote:
> There are 2 tables: person, sport
> sport:
> basketball
> soccer
> 
> person
> James      basketball
> James      hockey
> James      Kayak 
> melanie    waterpolo
> melanie    soccer
> mitch      kayak
> mitch      scubadiving
> mitch      mountainclimbing
> john       basketball
> john       volleyball
> john       soccer
> john       waterpolo
> jenny      football
> Harry      basketball
> Harry      soccer
> 
> 
> My problem is: 
> 1: how do you generate the persons that are capable of doing all of
>    the sports from the sports table; (outcome should be John and
>    Harry)

 SELECT person.name
 FROM   pers, sport
 WHERE  person.sport = sport.name
 GROUP BY person.name
 HAVING COUNT(*) = 2;

 "2" must be the number of records in the 'sport' table.
 So only person records are returned that match all (2) sports.


> 2: how do you generate the person(s) that are/is capable of doing no
>    more than the selected sports from the sport table; (outcome
>    should be Harry)

 SELECT person.name
 FROM   person LEFT JOIN sport
          ON person.sport = sport.name
 GROUP BY person.name
 HAVING COUNT(*) = 2
    AND count(sport.name) = 2;

If you're not familiar with GROUP BY and LEFT JOIN, it's maybe
instructive to look at these queries first:

1.)  SELECT *
     FROM person, sport;

2.)  SELECT *
     FROM person, sport
     WHERE  person.sport=sport.name;

3.)  SELECT *
     FROM person, sport
     WHERE  person.sport=sport.name
     ORDER BY person.name;

4.)  SELECT person.name, COUNT(*)
     FROM person, sport
     WHERE  person.sport=sport.name
     GROUP BY person.name;

Or for the other query:

1.)  SELECT *
     FROM person LEFT JOIN sport ON person.sport = sport.name;

2.)  SELECT *
     FROM person LEFT JOIN sport ON person.sport = sport.name
     ORDER BY person.name;

3.)  SELECT person.name, COUNT(*), COUNT(sport.name)
     FROM person LEFT JOIN sport ON person.sport = sport.name
     GROUP BY person.name;

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
SQL problemMaarten Roosendaal21 Sep
  • Re: SQL problemMartin Ramsch21 Sep
  • Re: SQL problemBob Kline21 Sep