How to find athletes who participate in both Basketball and Golf using MySQL

Data and Databases
1

I’m working with a MySQL database that has 3 related tables: ATHLETES, GAMES, and PARTICIPATION.

The ATHLETES table stores athlete information with columns (athlete_id, full_name):

(101, 'Mike Johnson')
(102, 'Sarah Wilson')
(103, 'Tom Brown')
(104, 'Lisa Davis')

The GAMES table contains game details with (game_id, game_name):

(201, 'Basketball')
(202, 'Golf')
(203, 'Baseball')

Since athletes can participate in multiple games, the PARTICIPATION table links them using (athlete_id, game_id):

(101, 202)
(101, 203)
(102, 203)

I need to write a query that returns the names of athletes who participate in BOTH Basketball AND Golf. Not one or the other, but specifically those who do both activities. What’s the best approach to accomplish this?

2

i would say using EXISTS subqueries is the way to go. just try something like this: SELECT full_name FROM ATHLETES a WHERE EXISTS (SELECT 1 FROM PARTICIPATION p JOIN GAMES g ON p.game_id=g.game_id WHERE p.athlete_id=a.athlete_id AND g.game_name='Basketball') AND EXISTS (SELECT 1 FROM PARTICIPATION p JOIN GAMES g ON p.game_id=g.game_id WHERE p.athlete_id=a.athlete_id AND g.game_name='Golf'). this should work!

3

cool ideas! quick question tho - what happens when your database gets massive? would EXISTS outperform GROUP BY at scale? also, looking at your sample data, none of those athletes actually play both sports based on what you’ve shown?

4

Here’s another way using GROUP BY with HAVING. Join all three tables, filter for Basketball and Golf, then group by athlete and count distinct games: SELECT a.full_name FROM ATHLETES a JOIN PARTICIPATION p ON a.athlete_id = p.athlete_id JOIN GAMES g ON p.game_id = g.game_id WHERE g.game_name IN ('Basketball', 'Golf') GROUP BY a.athlete_id, a.full_name HAVING COUNT(DISTINCT g.game_name) = 2. This makes sure the athlete plays both sports by counting distinct game names and requiring exactly 2.