mercredi 15 juin 2016

Selecting from 3 tables

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

  1. and won at least one of them
  2. 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