SQL Query Joins not working properly

Question

For the project I needed to create a database for the small Olympic Games. My ER diagram as shown ... link to the best picture: https://i.imgur.com/xgfurWO.png?1

I need to create a query that includes only competitors competing in more than one event (with only two competitors) and to list the name, event, venue and result.

The query below works because it should create records of 70 (competitors No.) with a score of 2 for competitors competing in two events ...

SELECT c.firstname, c.lastname, COUNT(r.competitorid)
FROM  COMPETITOR c LEFT OUTER JOIN ( REGISTRATION r LEFT OUTER JOIN  EVENT e 
               ON r.eventid = e.eventid ) ON r.competitorid = c.competitorid
GROUP BY c.firstname, c.lastname
ORDER BY c.firstname;
-----------------------------------------
NORRIS  HOLMWOOD    1
OCTAVIO MARTINEZ    1
ORFEO   SILVA       2
etc...

After including the event name in the query, it produces 72 results with competitors competing in two events listed twice, but COUNT () is 1 for everything.

SELECT c.firstname, c.lastname, e.eventname, COUNT(r.competitorid)
FROM  COMPETITOR c LEFT OUTER JOIN ( REGISTRATION r LEFT OUTER JOIN  EVENT e 
ON r.eventid = e.eventid ) ON r.competitorid = c.competitorid
GROUP BY c.firstname, c.lastname, e.eventname
ORDER BY 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 

. , 72 , .

SELECT c.firstname, c.lastname, e.eventname, COUNT(r.competitorid)
FROM  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 BY c.firstname, c.lastname, e.eventname
ORDER BY 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

, ? , , , , , (). , , , - .

Answer 1

: ( , , .) , Right join , , , . , , .

, 1: , , , .

, , , [ , ] , ( , , -, )

SELECT c.firstname, c.lastname, e.eventname, CE.TotalNumberOFEvents
FROM  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 this is a right join... on your qry.
  ON e.eventid = rs.eventid
 AND Rs.CompetitorID = C.CompetitorID  ---ADDED this and removed the () putting join critier under the joins. 
LEFT JOIN (SELECT count(RegistrationID) as TotalNumberOfEvents, CompetitorID 
           FROM Registration) as CE    --added this to get the # of events a competitor is in (independant of the event name listed)
  ON Ce.CompetitorID = C.CompetitorID
GROUP BY c.firstname, c.lastname, e.eventname
ORDER BY c.firstname;