Nolifepothead Nolifepothead - 4 months ago 15
SQL Question

SQL Query Joins not working properly

For a project I needed to create a DB for a small Olympics. My ER Diagram is as shown...
enter image description here
link to better picture: https://i.imgur.com/xgfurWO.png?1

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

The query below works as it should producing 70 (# of competitors) records with the count at 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 the 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


Then this is were things really get messed up. When I join the result table with the event table, the output stays the same with 72 records but the count is completely wrong.

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


My question is what am I doing wrong? The joins seem to be working at least partly how I want them to, the problem is with the messed up count(). It doesn't necessarily matter the numbers are bigger, the problem is the count seems to be fairly random for some reason.

Answer

Consider: (A result is also specific to a competitor so you missed that join criteria.) Also, Right join means you want all records from results and all records as other tables are LEFT joining to Competitor. Since a result logically is based on competitor, I'm guessing the right join is incorrect.

So two fold issue on counts 1 the missing join criteria was causing counts to inflate, and the right join may have also caused problems but since a result is dependent on a competitor, you may not see any issues.

If you want the number of events a competitor is registered, you need to derive that count desperately and then join back in (there are other ways but I generally prefer this method as it seems to be DB agnostic)

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 ()'s 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;