Hi I am trying to write a query that will return the gold, silver, and bronze medalists for each individual-based event.
The relational schemas are:
EVENT(Event_id, Event_title, Event_team, Event_gender, Sport_id)
GAME(Game_id, Gtype_id, Game_year, game_website, game_cancel, Country_id)
GAMETYPE(Gtype_id, Gtype_title)
SPORT(Sport_id, Sport_title, Gtype, id)
CONTESTANT(Cont_id, Rep_id, Comp_id, Team_id)
MEDAL(Medal_id, Medal_color, Cont_id)
REPRESENTATIVE(Rep_id, Athlete_id, Country_id, Game_id, Sport_id)
ATHLETE(Athlete_id, Athlete_lname, athlete_fname, Country_id)
COMPETITION(Comp_id, Event_id, Game_id)
COUNTRY(Country_id, Country_name)
So far I have:
SELECT GAME.Game_year, GAMETYPE.Gtype_title, SPORT.Sport_title, EVENT.Event_title, MEDAL.Medal_color, ATHLETE.Athlete_lname, ATHLETE.Athlete_fname, ATHLETE.Athlete_gender
FROM (((((GAMETYPE INNER JOIN GAME
ON GAMETYPE.Gtype_id=GAME.Gtype_id)
INNER JOIN SPORT
ON GAMETYPE.Gtype_id=SPORT.Gtype_id)
INNER JOIN EVENT
ON SPORT.Sport_id=EVENT.Sport_id)
INNER JOIN COMPETITION
ON EVENT.Event_id=COMPETITION.Event_id)
INNER JOIN ((ATHLETE INNER JOIN REPRESENTATIVE
ON ATHLETE.Athlete_id=REPRESENTATIVE.Athlete_id)
INNER JOIN CONTESTANT
ON REPRESENTATIVE.Rep_id=CONTESTANT.Rep_id)
ON COMPETITION.Comp_id=CONTESTANT.Comp_id)
INNER JOIN MEDAL
ON CONTESTANT.Cont_id=MEDAL.Cont_id
WHERE CONTESTANT.Team_id Is Null
AND CONTESTANT.Cont_id IN (SELECT Cont_id
FROM MEDAL)
GROUP BY GAME.Game_year, GAMETYPE.Gtype_title, SPORT.Sport_title, EVENT.Event_title, MEDAL.Medal_color, ATHLETE.Athlete_lname, ATHLETE.Athlete_fname, ATHLETE.Athlete_gender;
But it is returning multiple gold, silver, and bronze medalists for each event and I am really not sure how to fix this.
Any assistance would be appreciated.