Help with a query

[jozz3]

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.

[smantscheff]

Try "SELECT DISTINCT ... "

[jozz3]

I thought that too but it doesn't change anything.

[smantscheff]

In which fields do the seemingly duplicated result rows differ? This might lead you to a semantically wrong join (which I cannot tell without test data).
Maybe you could submit some test data to reproduce the problem (i.e. a complete mysql script for creating the tables, filling them and running the query).

[jozz3]

I have written seperate queries for each gold, silver, and bronze medals -

SELECT GAME.Game_year AS [YEAR], GAMETYPE.Gtype_title AS GAME, SPORT.Sport_title+' '+EVENT.Event_title AS EVENT, MEDAL.Medal_color AS GOLD, ATHLETE.Athlete_lname+' '+ATHLETE.Athlete_fname AS NAME
FROM (GAMETYPE INNER JOIN ((GAME INNER JOIN (EVENT INNER JOIN ((COMPETITION INNER JOIN CONTESTANT ON COMPETITION.Comp_id=CONTESTANT.Comp_id) INNER JOIN MEDAL ON CONTESTANT.Cont_id=MEDAL.Cont_id) ON EVENT.Event_id=COMPETITION.Event_id) ON GAME.Game_id=COMPETITION.Game_id) INNER JOIN (ATHLETE INNER JOIN REPRESENTATIVE ON ATHLETE.Athlete_id=REPRESENTATIVE.Athlete_id) ON (REPRESENTATIVE.Rep_id=CONTESTANT.Rep_id) AND (GAME.Game_id=REPRESENTATIVE.Game_id)) ON GAMETYPE.Gtype_id=GAME.Gtype_id) INNER JOIN SPORT ON (SPORT.Sport_id=REPRESENTATIVE.Sport_id) AND (SPORT.Sport_id=EVENT.Sport_id) AND (GAMETYPE.Gtype_id=SPORT.Gtype_id)
WHERE (((MEDAL.Medal_color)="Gold") AND ((CONTESTANT.Team_id) Is Null))
ORDER BY GAME.Game_year, EVENT.Event_title;

SELECT GAME.Game_year AS [YEAR], GAMETYPE.Gtype_title AS GAME, SPORT.Sport_title+' '+EVENT.Event_title AS EVENT, MEDAL.Medal_color AS SILVER, ATHLETE.Athlete_lname+' '+ATHLETE.Athlete_fname AS NAME
FROM (GAMETYPE INNER JOIN ((GAME INNER JOIN (EVENT INNER JOIN ((COMPETITION INNER JOIN CONTESTANT ON COMPETITION.Comp_id=CONTESTANT.Comp_id) INNER JOIN MEDAL ON CONTESTANT.Cont_id=MEDAL.Cont_id) ON EVENT.Event_id=COMPETITION.Event_id) ON GAME.Game_id=COMPETITION.Game_id) INNER JOIN (ATHLETE INNER JOIN REPRESENTATIVE ON ATHLETE.Athlete_id=REPRESENTATIVE.Athlete_id) ON (GAME.Game_id=REPRESENTATIVE.Game_id) AND (REPRESENTATIVE.Rep_id=CONTESTANT.Rep_id)) ON GAMETYPE.Gtype_id=GAME.Gtype_id) INNER JOIN SPORT ON (GAMETYPE.Gtype_id=SPORT.Gtype_id) AND (SPORT.Sport_id=EVENT.Sport_id) AND (SPORT.Sport_id=REPRESENTATIVE.Sport_id)
WHERE (((MEDAL.Medal_color)="Sliver") AND ((CONTESTANT.Team_id) Is Null))
ORDER BY GAME.Game_year, EVENT.Event_title;

SELECT GAME.Game_year AS [YEAR], GAMETYPE.Gtype_title AS GAME, SPORT.Sport_title + ' ' + EVENT.Event_title AS EVENT, MEDAL.Medal_color AS BRONZE, ATHLETE.Athlete_lname + ' ' + ATHLETE.Athlete_fname AS NAME
FROM (GAMETYPE INNER JOIN ((GAME INNER JOIN (EVENT INNER JOIN ((COMPETITION INNER JOIN CONTESTANT ON COMPETITION.Comp_id=CONTESTANT.Comp_id) INNER JOIN MEDAL ON CONTESTANT.Cont_id=MEDAL.Cont_id) ON EVENT.Event_id=COMPETITION.Event_id) ON GAME.Game_id=COMPETITION.Game_id) INNER JOIN (ATHLETE INNER JOIN REPRESENTATIVE ON ATHLETE.Athlete_id=REPRESENTATIVE.Athlete_id) ON (REPRESENTATIVE.Rep_id=CONTESTANT.Rep_id) AND (GAME.Game_id=REPRESENTATIVE.Game_id)) ON GAMETYPE.Gtype_id=GAME.Gtype_id) INNER JOIN SPORT ON (SPORT.Sport_id=REPRESENTATIVE.Sport_id) AND (SPORT.Sport_id=EVENT.Sport_id) AND (GAMETYPE.Gtype_id=SPORT.Gtype_id)
WHERE (((MEDAL.Medal_color)="Bronze") AND ((CONTESTANT.Team_id) Is Null))
ORDER BY GAME.Game_year, EVENT.Event_title;

These return the correct results, but I am unsure how to join the three to return YEAR, GAME, EVENT, GOLD, NAME, SILVER, NAME, BRONZE, NAME.

[smantscheff]

I doubt that your code returns the correct results - there is a typo "Sliver" which means that your code is not tested.
If the three queries work correctly you can combine them using UNION and select the desired fields from the resulting UNION.

[jozz3]

There was a typo in the database so it does work. In order to left join each query, what do I need to change to go from INNER JOIN to LEFT JOIN?

SELECT GAME.Game_year AS [YEAR], GAMETYPE.Gtype_title AS GAME, SPORT.Sport_title+' '+EVENT.Event_title AS EVENT, MEDAL.Medal_color AS GOLD, ATHLETE.Athlete_lname+' '+ATHLETE.Athlete_fname AS NAME, CONTESTANT.Comp_id
FROM (GAMETYPE INNER JOIN ((GAME INNER JOIN (EVENT INNER JOIN ((COMPETITION INNER JOIN CONTESTANT ON COMPETITION.Comp_id=CONTESTANT.Comp_id) INNER JOIN MEDAL ON CONTESTANT.Cont_id=MEDAL.Cont_id) ON EVENT.Event_id=COMPETITION.Event_id) ON GAME.Game_id=COMPETITION.Game_id) INNER JOIN (ATHLETE INNER JOIN REPRESENTATIVE ON ATHLETE.Athlete_id=REPRESENTATIVE.Athlete_id) ON (REPRESENTATIVE.Rep_id=CONTESTANT.Rep_id) AND (GAME.Game_id=REPRESENTATIVE.Game_id)) ON GAMETYPE.Gtype_id=GAME.Gtype_id) INNER JOIN SPORT ON (SPORT.Sport_id=REPRESENTATIVE.Sport_id) AND (SPORT.Sport_id=EVENT.Sport_id) AND (GAMETYPE.Gtype_id=SPORT.Gtype_id)
WHERE (((MEDAL.Medal_color)="Bronze") AND ((CONTESTANT.Team_id) Is Null))
ORDER BY GAME.Game_year, EVENT.Event_title;