Can this sql query be simplified?
- by Bas
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.
