slick slick - 2 years ago 89
SQL Question

Inner join an inner join with another inner join

I'm wondering if it is possible to inner join an inner join with another inner join.

I have a database of 3 tables:

people

athletes

coaches

Every athlete or coach must exist in the people table, but there are some people who are neither coaches nor athletes.

What I am trying to do is find a list of people who are active (meaning play or coach) in at least 3 different sports. The definition of active is they are either coaches, athletes or both a coach and an athlete for that sport.

The person table would consist of (id, name, height)
the athlete table would be (id, sport)
the coaching table would be (id, sport)

I have created 3 inner joins which tell me who is both a coach and and an athlete, who is just a coach and who is just an athlete.

This is done via inner joins.

For example,

1) who is both a coach and an athlete

select
person.id,
person.name,
coach.sport as 'Coaches and plays this sport'
from coach
inner join athlete
on coach.id = athlete.id
and coach.sport = athlete.sport
inner join person
on athlete.id = person.id


That brings up a list of everyone who both coaches and plays the same sport.

2) To find out who only coaches sports, I have used inner joins as below:

select
person.id,
person.name,
coach.sport as 'Coaches this sport'
from coach
inner join person
on coach.id = person.id


3) Then to find out who only plays sports, I've got the same as 2) but just tweaked the words

select
person.id,
person.name,
athlete.sport as 'Plays this sport'
from athlete
inner join person
on athlete.id = person.id


The end result is now I've got:
1) persons who both play and coach the same sport
2) persons who coach a sport
3) persons who play a sport

What I would like to know is how to find a list of people who play or coach at least 3 different sports? I can't figure it out because if someone plays and coaches a sport like hockey in table 1, then I don't want to count them in table 2 and 3.

I tried using these 3 inner joins to make a massive join table so that I could pick the distinct values but it is not working.

Is there an easier way to go about this without making sub-sub-queries?

Answer Source
CREATE TABLE #person (Id INT, Name VARCHAR(50));
CREATE TABLE #athlete (Id INT, Sport VARCHAR(50));
CREATE TABLE #coach (Id INT, Sport VARCHAR(50));

INSERT INTO #person (Id, Name) VALUES(1, 'Bob'); 
INSERT INTO #person (Id, Name) VALUES(2, 'Carol');
INSERT INTO #person (Id, Name) VALUES(2, 'Sam');

INSERT INTO #athlete (Id, Sport) VALUES(1, 'Golf');
INSERT INTO #athlete (Id, Sport) VALUES(1, 'Football');
INSERT INTO #coach (Id, Sport) VALUES(1, 'Tennis');
INSERT INTO #athlete (Id, Sport) VALUES(2, 'Tennis');
INSERT INTO #coach (Id, Sport) VALUES(2, 'Tennis');
INSERT INTO #athlete (Id, Sport) VALUES(2, 'Swimming');

-- so Bob has 3 sports, Carol has only 2 (she both coaches and plays Tennis)

SELECT p.Id, p.Name
FROM 
(
    SELECT Id, Sport
    FROM #athlete
    UNION -- this has an implicit "distinct"
    SELECT Id, Sport
    FROM #coach
) a
INNER JOIN #person p ON a.Id = p.Id
GROUP BY p.Id, p.Name
HAVING COUNT(*) >= 3

-- returns 1, Bob
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download