CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designmysql-workbench
blueGOLD
blueGOLD

Reputation: 105

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 ?

Upvotes: 0

Views: 40

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10277

If you remove your WHERE medal = 'gold' and look at your results, it should give you a clue about the problem. This is often the best way to troubleshoot.

Anyway, you need to join results to athletes on athleteID instead of eventID. This is because you're concerned with which athletes won gold not which events had a gold medal.

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

Upvotes: 1

Alex
Alex

Reputation: 17289

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';

Upvotes: 1

Related Questions