Return all rows in JOIN using AVG

[jcanker]

Somewhat of a revist from my last post, but on to the next step and bigger problems

I'm trying to create a sortable table that shows all the players' averaged evalulations

 

players were likely evaluated by multiple adults, therefore there are multiple "evaluations"

In TABLE: evaldata each evaluation for each player is a row

 

I can get the AVG for each registered player within a specific age group perfectly using GROUP BY, and I can get the player's name and userID  and from TABLE users with their raw eval data just fine with this query:

SELECT users.fName, users.lName, evaldata.cat1Val
FROM users
LEFT OUTER JOIN registrations ON registrations.userID=users.userID
LEFT OUTER JOIN evaldata ON evaldata.userID=registrations.userID
WHERE registrations.ageGroupID=5;

 

But the coaches need a table with the averaged values for each rating as a singe row.  

This works:

SELECT users.fName, users.lName, AVG(evaldata.cat1Val)
FROM users
LEFT OUTER JOIN registrations ON registrations.userID=users.userID
LEFT OUTER JOIN evaldata ON evaldata.userID=registrations.userID
WHERE registrations.ageGroupID=5 

 

THE PROBLEM IS that I also need to include players who appear in TABLE registrations but do not have any evaluation data (mostly because they missed the evaluation day).

 

That last query returns all the players who have eval data, but ignores the players who don't have eval data and therefore don't have any rows in TABLE evaldata

 

MY QUESTION IS:  how can I get this query so that it also returns the players who are in TABLE:registrations but don't have any rows with their userID in TABLE evaldata?

 

Strangely enough, if I add "GROUP BY evaldata.userID" at the end, it returns ONE ROW of the first userID without any data, then the AVG rows of players who do have data.  

 

Am I just joining these in the wrong order?

[jcanker]

BAH...why is it that I always spend an hour or more trying to solve these on my own, give up in frustration and turn here for help, and THEN find the answer just a few minutes later!?

 

If I group by registrations.userID instead of evaldata.userID, the join returns all the rows just as I need:

 

SELECT users.fName, users.lName, AVG(evaldata.cat1Val)
FROM users
LEFT OUTER JOIN registrations ON registrations.userID=users.userID
LEFT OUTER JOIN evaldata ON evaldata.userID=registrations.userID
WHERE registrations.ageGroupID=5 GROUP BY registrations.userID

 

Returns the fName, lName, and AVG of the player's evaluations in category 1 for every player registered in that age group.  If player doesn't have eval data in that category it returns fName, lName and NULL for cat1Val.