mysql - SQL Query Joins not working properly -

for project needed create db small olympics. er diagram shown... link better picture: https://i.imgur.com/xgfurwo.png?1

i need create query includes competitors competing in more 1 event (that's 2 competitors) , list name, event, venue, , result.

the query below works should producing 70 (# of competitors) records count @ 2 competitors competing in 2 events...

select c.firstname, c.lastname, count(r.competitorid)  competitor c left outer join ( registration r left outer join  event e                 on r.eventid = e.eventid ) on r.competitorid = c.competitorid group c.firstname, c.lastname order c.firstname; ----------------------------------------- norris  holmwood    1 octavio martinez    1 orfeo   silva       2 etc... 

after including event name in query produces 72 results competitors competing in 2 events listed twice count() 1 everything.

select c.firstname, c.lastname, e.eventname, count(r.competitorid)  competitor c left outer join ( registration r left outer join  event e  on r.eventid = e.eventid ) on r.competitorid = c.competitorid group c.firstname, c.lastname, e.eventname order c.firstname; ---------------------------------------------------------------- norris  holmwood    100 meter butterfly 1 octavio martinez    floor exercise  1 orfeo   silva   100 meter butterfly 1 orfeo   silva   400 meter indiv medley  1 poncio  asis    pommel horse    1 poncio  barros  long jump   1  

then things messed up. when join result table event table, output stays same 72 records count wrong.

select c.firstname, c.lastname, e.eventname, count(r.competitorid)  competitor c left outer join ( registration r left outer join (     event e right outer join result rs on e.eventid = rs.eventid)     on r.eventid = e.eventid ) on r.competitorid = c.competitorid group c.firstname, c.lastname, e.eventname order c.firstname; ----------------------------------------------------------------------- nikolai mikhailov   pommel horse        6 noemi   pelaez  balance beam            7 norris  holmwood    100 meter butterfly 6 octavio martinez    floor exercise      6 orfeo   silva   100 meter butterfly     6 orfeo   silva   400 meter indiv medley  6 poncio  asis    pommel horse            6 

my question doing wrong? joins seem working @ least partly how want them to, problem messed count(). doesn't matter numbers bigger, problem count seems random reason.

consider: (a result also specific competitor missed join criteria.) also, right join means want records results , records other tables left joining competitor. since result logically based on competitor, i'm guessing right join incorrect.

so 2 fold issue on counts 1 missing join criteria causing counts inflate, , right join may have caused problems since result dependent on competitor, may not see issues.

if want number of events competitor registered, need derive count [desperately spelling error funny take out] separately , join in (there other ways prefer method seems db agnostic)

select c.firstname, c.lastname, e.eventname, ce.totalnumberofevents  competitor c  left outer join registration r    on r.competitorid = c.competitorid left outer join event e    on r.eventid = e.eventid  left outer join result rs    --not sure why right join... on qry.   on e.eventid = rs.eventid  , rs.competitorid = c.competitorid  ---added , removed ()'s putting join critier under joins.  left join (select count(registrationid) totalnumberofevents, competitorid             registration) ce    --added # of events competitor in (independant of event name listed)   on ce.competitorid = c.competitorid group c.firstname, c.lastname, e.eventname order c.firstname;