I've finally solved this (right in the middle of corporate taxes - UGH!).
Rick James on the MySql forum had it. He just looked at my query and saw what was wrong.
You can't do 2 exclusive INNER JOINS on the same table. They'll just cancel each other out.
You can't do LEFT JOINS on the same table either. You'll just get all records on the left side (ie relations).
This gives me exactly what I need. I tested it and compared it to the actual scores & relationships. It's right.
It even handles situations where there was no division and/or no team and/or no organization. All the averages are correct. I added the table organizations to be able to make 3 tiers. I've split the relations table up into multiple tables. rel_divisions_rl(RelationID, DivisionID) rel_teams_rl(RelationID, TeamID) rel_organizations_rl(RelationID, OrgID) If anybody knows how to make this faster without altering the results I sure would appreciate learning about it. SELECT divisions.RecordID AS DivisionID, teams.RecordID AS TeamID, (organizations.RecordID) AS OrgID, MAX(divisions.Division), MAX(teams.Team), MAX(organizations.OrgName), AVG(scores.Score) AS Score FROM scores INNER JOIN (((relations LEFT JOIN (rel_divisions_rl LEFT JOIN divisions ON rel_divisions_rl.DivisionID = divisions.RecordID) ON rel_divisions_rl.RelationID = relations.RecordID) LEFT JOIN (rel_teams_rl LEFT JOIN teams ON rel_teams_rl.TeamID = teams.RecordID) ON rel_teams_rl.RelationID = relations.RecordID) LEFT JOIN (rel_organizations_rl LEFT JOIN organizations ON rel_organizations_rl.OrgID = organizations.RecordID) ON rel_organizations_rl.RelationID = relations.RecordID) ON scores.RelationID = relations.RecordID GROUP BY DivisionID, TeamID, OrgID ORDER BY DivisionID, TeamID, OrgID