Can this sql query be simplified?

Question

I have the following tables:
Person, {"Id", "Name", "LastName"}
Sports, {"Id" "Name", "Type"}
SportsPerPerson, {"Id", "PersonId", "SportsId"}

For my query I want to get all the Persons that excersise a specific Sport whereas I only have the Sports "Name" attribute at my disposal. To retrieve the correct rows I've figured out the following queries:

SELECT *
FROM Person
WHERE Person.Id in 
(
    SELECT SportsPerPerson.PersonId FROM SportsPerPerson
    INNER JOIN Sports on SportsPerPerson.SportsId = Sports.Id
    WHERE Sports.Name = 'Tennis'
)
AND Person.Id in 
(
    SELECT SportsPerPerson.PersonId FROM SportsPerPerson
    INNER JOIN Sports on SportsPerPerson.SportsId = Sports.Id
    WHERE Sports.Name = 'Soccer'
)

OR

SELECT *
FROM Person
WHERE Id IN
    (SELECT PersonId FROM SportsPerPerson WHERE SportsId IN 
        (SELECT Id FROM Sports WHERE Name = 'Tennis'))
AND Id IN
    (SELECT PersonId FROM SportsPerPerson WHERE SportsId IN 
        (SELECT Id FROM Sports WHERE Name = 'Soccer'))

Now my question is, isn't there an easier way to write this query? Using just OR won't work because I need the person who play 'Tennis' AND 'Soccer'. But using AND also doesn't work because the values aren't on the same row.

Answer 1

You should use two joins in the query:

SELECT *
FROM Person p INNER JOIN SportsPerPerson spp1 ON (p.PersonId = spp1.PersonId)
              INNER JOIN Sports s1 ON (s1.SportsIN = spp1.SportId)
              INNER JOIN SportsPerPerson spp2 ON (p.PersonId = spp2.PersonId)
              INNER JOIN Sports s2 ON (s2.SportId = spp2.SportId)
    WHERE s1.Name = 'Tennis' AND s2.Name='Soccer'

Answer 2

You can use another JOIN to avoid the second IN. The sub-select only returns those persons that play both Tennis and Soccer:

SELECT *
FROM Person
WHERE Person.Id IN
(
    SELECT spp1.PersonId
    FROM SportsPerPerson spp1
    JOIN SportsPerPerson spp2 ON ( spp2.PersonId = spp1.PersonId )
    JOIN Sports s1 on spp1.SportsId = s1.Id
    JOIN Sports s2 on spp2.SportsId = s2.Id
    WHERE s1.Name = 'Tennis'
      AND s2.Name = 'Soccer'
)

Answer 3

The trick is to use aliases so that you can use the same tables multiple times:

SELECT p.*
FROM Person p
INNER JOIN SportsPerPerson spa
  ON p.Id = spa.PersonId
INNER JOIN Sports sa
  ON spa.SportsId = sa.Id
INNER JOIN SportsPerPerson spb
  ON p.Id = spb.PersonId
INNER JOIN Sports sb
  ON spb.SportsId = sb.Id
WHERE
  sa.Name = 'Tennis'
  AND sb.Name = 'Soccer'

Answer 4

This:

SELECT  *
FROM    Person p
WHERE   (
        SELECT  COUNT(*)
        FROM    Sports s
        JOIN    SportsPerPerson sp
        ON      sp.SportsID = s.id
        WHERE   s.name IN ('Tennis', 'Soccer')
                AND sp.PersonID = p.id
        ) = 2

or this:

SELECT  p.*
FROM    (
        SELECT  sp.PersonID
        FROM    Sports s
        JOIN    SportsPerPerson sp
        ON      sp.SportsID = s.id
        WHERE   s.name IN ('Tennis', 'Soccer')
        GROUP BY
                sp.PersonID
        HAVING  COUNT(*) = 2
        ) q
JOIN    person p
ON      p.id = q.personID

You need to declare a UNIQUE KEY or a PRIMARY KEY on SportsPerPerson (sportsid, personid) for this to work correctly and fast.

Answer 5

The query you need is:

SELECT p.ID, p.Name, p.LastName
FROM Person p
JOIN SportsPerPerson sp ON p.ID = sp.PersonID 
JOIN Sports s ON sp.SportsID = s.ID
WHERE s.Name = 'Football'

That said, as an aside, the ID key on the SportsPerPerson table is entirely unnecessary to implement the many to many relationship you have. Using the PersonID and SportID columns as a composite primary key would be enough.