Inner join an inner join with another inner join

Question

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 1

The coaches & athletes, ie people who are coaches or athletes, are relevant to your answer. That is union (rows in one or another), not (inner) join rows in one and another). (Although outer join involves a union, so there is a complicated way to use it here.) But there's no point in getting that by unioning only-coaches, only-athletes & coach-athletes.

Idiomatic is to group & count the union of Athletes & Coaches.

select id
from (select * from Athletes union select * from Coaches) as u
group by id
having COUNT(*) >= 3

Alternatively, you want ids of people who coach or play a 1st sport and coach or play a 2nd sport and coach or play a 3rd sport where the sports are all different.

with u as (select * from Athletes union select * from Coaches)
select u1.id
from u u1
join u u2 on u1.id = u2.id
join u u3 on u2.id = u3.id
where u1.sport <> u2.sport and u2.sport <> u3.sport and u1.sport <> u3.sport

If you wanted names you would join that with People.

Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097)

Answer 2

I have created a SQL with some test data - should work in your case:

Connecting the two results in the subselect with UNION:
UNION will return just non-duplicate values. So every sport will be just counted once.

Finally just grouping the resultset by person.Person_id and person.name. Due to the HAVING clause, just persons with 3 or more sports will be returned-

CREATE TABLE person
(
   Person_id int
  ,name varchar(50)
  ,height int
)

CREATE TABLE coach
(
    id int
   ,sport varchar(50)
)

CREATE TABLE athlete
(
    id int
   ,sport varchar(50)
)

INSERT INTO person VALUES
(1,'John', 130),
(2,'Jack', 150),
(3,'William', 170),
(4,'Averel', 190),
(5,'Lucky Luke', 180),
(6,'Jolly Jumper', 250),
(7,'Rantanplan ', 90)

INSERT INTO coach VALUES
(1,'Football'),
(1,'Hockey'),
(1,'Skiing'),
(2,'Tennis'),
(2,'Curling'),
(4,'Tennis'),
(5,'Volleyball')


INSERT INTO athlete VALUES
(1,'Football'),
(1,'Hockey'),
(2,'Tennis'),
(2,'Volleyball'),
(2,'Hockey'),
(4,'Tennis'),
(5,'Volleyball'),
(3,'Tennis'),
(6,'Volleyball'),
(6,'Tennis'),
(6,'Hockey'),
(6,'Football'),
(6,'Cricket')


          SELECT person.Person_id
                ,person.name
            FROM person
      INNER JOIN (
                    SELECT id
                          ,sport
                      FROM athlete
                   UNION
                    SELECT id
                          ,sport
                      FROM coach          
                 ) sports
               ON sports.id = person.Person_id
        GROUP BY person.Person_id
                ,person.name   
          HAVING COUNT(*) >= 3
        ORDER BY Person_id

Answer 3

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

Answer 4

What I would like to know is how to find a list of people who play / 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.

you can do something like this

select p.id,min(p.name) name
from 
person p inner join
(
select id,sport from athlete
union
select id,sport from coach
)
ca
on ca.id=p.id
group by p.id
having count(ca.sport)>2