I have a database schema like below.
What is the SQL query to find people who belong to two different teams and are coach role in one team and player role in another team?
Person ------------- Id int Name varchar(255) Member ------------- Id int PersonId int TeamId int Role varchar(255) Team ------------- Id int TeamName varchar(255)
What I can come up with so far is
SELECT * FROM Person p JOIN Member m ON p.Id = m.PersonId GROUP BY p.Id HAVING COUNT(m.Id) > 1;
I am not sure this is a correct approach. Can anyone please help me with this?
Thanks
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
The query you wrote will return any player which has more than one entry in the Member table. For example, if you had a person with a 'player' role in 3 different teams, your query will return that person. Of course, under the assumption that this case could happen.
If you want to select people which are players in AT LEAST one team and coaches in AT LEAST one team, you could use:
SELECT p.Id, p.Name
FROM Person p
WHERE EXISTS (SELECT 1 -- select anything for checking if the row exists
FROM Member m
WHERE m.PersonId = p.Id
AND m.Role LIKE 'coach')
AND EXISTS (SELECT 1
FROM Member m
WHERE m.PersonId = p.Id
AND m.Role LIKE 'player')
Note that this query assumes there are no Member entries in which person is a player and a coach on the same team. If there are such entries, query will return those people as well.
For easier role management, I would also suggest moving roles to a separate table, and using their Ids for comparison instead (m.Role = [id of the role in 'Role' table]).
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0