benjamin benjamin - 5 months ago 7
MySQL Question

Selecting various values from an event

I have got these 3 tables below, i want to list all athletes who won Gold

Athletes
+------------+-----------------+
| athletesID | athletesName |
+------------+-----------------+
| jg | justin gatlin |
| ms | maria sharapova |
| ub | usain bolt |
| vw | venus williams |
+------------+-----------------+

events
+---------+-----------+---------------------+
| eventID | eventName | athletes_athletesID |
+---------+-----------+---------------------+
| ev1 | tennis | ms |
| ev1 | tennis | vw |
| ev2 | mens 100m | jg |
| ev2 | mens 100m | ub |
+---------+-----------+---------------------+
results

+-----------+--------+----------------+----------------------------+
| resultsID | Medal | events_eventID | events_athletes_athletesID |
+-----------+--------+----------------+----------------------------+
| results1 | silver | ev1 | ms |
| results1 | Gold | ev1 | vw |
| results2 | silver | ev2 | jg |
| results2 | Gold | ev2 | ub |
+-----------+--------+----------------+----------------------------+


I have used this code below so far but this lists all the silver medalists as having won Gold

SELECT
athletesID, athletesName, medal
FROM
myoly.athletes
JOIN
myoly.events ON athletes.athletesID = events.athletes_athletesID
JOIN
myoly.results ON myoly.events.eventID = myoly.results.events_eventID
WHERE
medal = 'gold';


How can I list all athletes who won Gold only ?

Answer
SELECT 
    a.athletesID, 
    a.athletesName, 
    r.medal
FROM results r
LEFT JOIN athletes a
ON a.athletesID  = r.events_athletes_athletesID 
WHERE r.medal = 'Gold';