Why in the world do these tables use MyISAM?
What MySQL version are you running?
> The closest I've gotten was with my SELECT in
> the JOIN which you see in my original post.
Which aggregated across Joins thereby giving incorrect values.
Your DDL lets me fill in Join arguments presumptively ...
SELECT
Tier1ID, Tier1Val, Tier2ID, Tier2Val, Tier3ID, Tier3Val,
AVG(Score) AS ScoreAvg, COUNT(Score) AS ScoreCnt
FROM (
SELECT
attrib_division_rl.FieldID As Tier1ID,
tblTier1.Entry AS Tier1Val,
attrib_organization_rl.FieldID As Tier2ID,
CONCAT(tblTier2.Name, ' ', IFNULL(tblTier2.SubName, '')) AS Tier2Val,
tblTier3.EmployeeID AS Tier3ID,
TRIM(CONCAT(tblTier3.LName, ', ', IFNULL(tblTier3.FName, ''), ' ',
IFNULL(tblTier3.MidInit, ''))) AS Tier3Val,
answers.Score AS Score
FROM answers
JOIN attrib_division_rl
ON answers.AttributionID=attrib_division_rl.AttributionID
JOIN division AS tblTier1
ON attrib_division_rl.FieldID = tblTier1.RecordID
LEFT JOIN attrib_organization_rl
ON attrib_organization_rl.attributionID=attrib_division_rl.attributionID
JOIN organization AS tblTier2
ON attrib_organization_rl.FieldID = tblTier2.RecordID
LEFT JOIN employee AS tblTier3
ON answers.employeeID=tblTier3.employeeID
) AS x
GROUP BY Tier1ID, Tier1Val, Tier2ID, Tier2Val, Tier3ID, Tier3Val
ORDER BY Tier1Val, Tier2Val, Tier3Val;
... it parses, but of course you should check them for accuracy. If the query gives the same result with and without Tier...Val columns in the Group By clause, you've demonstrated they can be done without (see my 1st reply above)
> I can give you access to the test database but I'd
> need an IP to grant permission to access the db server.
Except for the occasional appearance of MySQL staff, we're volunteers here. MySQL is open source, the basic idea is that everybody can learn and anybody can help. When direct db access is necessary, we've gone private, and that's when the meter turns on ... perhaps you'll want to postpone that till there's no alternative. A simpler & cheaper alternative for you is to add a bit of data---just enough rows of dummy data to generate a meaningful and perfectly predictable query result---to sample instances of the six tables in a dummy database, and post a mysqldump of that small dataset here.
Edited 1 time(s). Last edit at 05/31/2020 06:42PM by Peter Brawley.