Can this sql query be simplified?
I have the following tables:
Person, {"Id", "Name", "LastName"}
Sports, {"Id" "Name", "Type"}
SportsPerPerson, {"Id", "PersonId", "SportsId"}
At my request, I want to get all Faces that crowd out a specific sport, whereas I only have the "Name" attribute in the sport. To get the correct lines I 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 only OR will not work because I need someone who plays Tennis and Football. But using AND also doesn't work because the values are not on the same line.
a source to share
You must use two queries in your request:
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'
a source to share
You can use the other JOIN
to avoid the second IN
. The sub-selection only returns those persons who play tennis and football:
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'
)
a source to share
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'
a source to share
It:
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 that:
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 UNIQUE KEY
or PRIMARY KEY
on SportsPerPerson (sportsid, personid)
for this to work correctly and quickly.
a source to share
Required query:
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 completely unnecessary to implement many, many relationships. Using the PersonID and SportID columns as the composite primary key would be sufficient.
a source to share