I have the following tables below.
athletes
+------------+-----------------+---------------+-------------------------+
| idATHLETES | ATHLETENAME | TEAMS_idTEAMS | TEAMS_COUNTRY_idCOUNTRY |
+------------+-----------------+---------------+-------------------------+
| JG | JUSTIN GATLIN | USA-TF-MEN | USA |
| MS | MARIA SHARAPOVA | RUS-WTA | RUS |
| SW | SERENA WILLIAMS | USA-WTA | USA |
| UB | USAIN BOLT | JAM-TF-MEN | JAM |
| VW | VENUS WILLIAMS | USA-WTA | USA |
+------------+-----------------+---------------+-------------------------+
events
+------------+---------------+---------------------+------------------------+----------------------------------+
| idEVENTS | EVENTNAME | ATHLETES_idATHLETES | ATHLETES_TEAMS_idTEAMS | ATHLETES_TEAMS_COUNTRY_idCOUNTRY |
+------------+---------------+---------------------+------------------------+----------------------------------+
| ATH | ATHLETICS | JG | USA-TF-MEN | USA |
| ATH | ATHLETICS | UB | JAM-TF-MEN | JAM |
| TEN | TENNIS | MS | RUS-WTA | RUS |
| TEN | TENNIS | VW | USA-WTA | USA |
| TEN-DOUBLE | TENNIS DOUBLE | SW | USA-WTA | USA |
| TEN-DOUBLE | TENNIS DOUBLE | VW | USA-WTA | USA |
+------------+---------------+---------------------+------------------------+----------------------------------+
results
+-----------+-------------+--------+-----------------+----------------------------+-------------------------------+-----------------------------------------+
| idRESULTS | STATUS | MEDALS | EVENTS_idEVENTS | EVENTS_ATHLETES_idATHLETES | EVENTS_ATHLETES_TEAMS_idTEAMS | EVENTS_ATHLETES_TEAMS_COUNTRY_idCOUNTRY |
+-----------+-------------+--------+-----------------+----------------------------+-------------------------------+-----------------------------------------+
| results1 | DID-NOT-WIN | SILVER | TEN | MS | RUS-WTA | RUS |
| results1 | WON | GOLD | TEN | VW | USA-WTA | USA |
| results2 | DID-NOT-WIN | BRONZE | ATH | JG | USA-TF-MEN | USA |
| results2 | WON | GOLD | ATH | UB | JAM-TF-MEN | JAM |
| results3 | WON | GOLD | TEN-DOUBLE | SW | USA-WTA | USA |
| results3 | WON | GOLD | TEN-DOUBLE | VW | USA-WTA | USA |
+-----------+-------------+--------+-----------------+----------------------------+-------------------------------+-----------------------------------------+
How can I get a list Athletes who participate in more than one Event
- and won at least one of them
- and won none of them
I have come out with this code below but this returns the wrong output ?
SELECT idATHLETES, ATHLETENAME, EVENTNAME FROM athletes
JOIN EVENTS ON idATHLETES = ATHLETES_idATHLETES
JOIN RESULTS ON events.ATHLETES_idATHLETES = RESULTS.EVENTS_ATHLETES_idATHLETES
WHERE idEVENTS >=2 AND STATUS = 'WON'
Aucun commentaire:
Enregistrer un commentaire