benjamin benjamin - 5 months ago 16
MySQL Question

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'

Answer

Athletes who participated in more than one Event and won at least one of them:

select a.idATHLETES as id,a.ATHLETENAME as Name,count(*) as evtCount, 
SUM(CASE WHEN r.STATUS = 'WON' THEN 1 ELSE 0 END) as victoryCount 
from athletes a 
join  EVENTS e 
on e.ATHLETES_idATHLETES = a.idATHLETES and e.ATHLETES_TEAMS_idTEAMS = a.TEAMS_idTEAMS and e.ATHLETES_TEAMS_COUNTRY_idCOUNTRY = a.TEAMS_COUNTRY_idCOUNTRY 
join RESULTS r 
on r.EVENTS_idEVENTS=e.idEVENTS and r.EVENTS_ATHLETES_idATHLETES=e.ATHLETES_idATHLETES -- etc 
group by a.idATHLETES,a.ATHLETENAME 
having evtCount>1 and victoryCount>0 
order by a.idATHLETES,a.ATHLETENAME; 

Results:

+----+----------------+----------+--------------+
| id | Name           | evtCount | victoryCount |
+----+----------------+----------+--------------+
| VW | VENUS WILLIAMS |        2 |            2 |
+----+----------------+----------+--------------+

Athletes who participated in more than one Event and won none:

select a.idATHLETES as id,a.ATHLETENAME as Name,count(*) as evtCount, 
SUM(CASE WHEN r.STATUS = 'WON' THEN 1 ELSE 0 END) as victoryCount 
from athletes a 
join  EVENTS e 
on e.ATHLETES_idATHLETES = a.idATHLETES and e.ATHLETES_TEAMS_idTEAMS = a.TEAMS_idTEAMS and e.ATHLETES_TEAMS_COUNTRY_idCOUNTRY = a.TEAMS_COUNTRY_idCOUNTRY 
join RESULTS r 
on r.EVENTS_idEVENTS=e.idEVENTS and r.EVENTS_ATHLETES_idATHLETES=e.ATHLETES_idATHLETES -- etc 
group by a.idATHLETES,a.ATHLETENAME 
having evtCount>1 and victoryCount=0 
order by a.idATHLETES,a.ATHLETENAME; 

Results:

no rows returned, you lack data for that

Notes

This is just a group by with having, and conditional aggregation (plus really messed up column names) :p

Comments