MySQL Forums
Forum List  »  General

Help - complex joins
Posted by: Bad Programmer
Date: May 27, 2020 01:44PM

I've got 3 tables:

Both attrib tables have an AttributionID, the employee table has an EmployeeID

I have a table called answers with an AttributionID and an EmployeeID.

This SQL does not work correctly.
The averages and scores are completely off.
For a given record it should be all the answers for the given division, organization & employee.
I'm getting wrong averages and counts that are way too high.
How can I write this (or any other technique) to get this correct?

AVG(answers.Score) AS ScoreAvg, CNT(answers.Score), attrib_division_rl.ID As DivisionID, attrib_organization_rl.ID AS OrganizationID, EmployeeID

FROM answers INNER JOIN attrib_division_rl
LEFT JOIN attrib_organization_rl
LEFT JOIN employees AS tblEmp
ON attrib_organization_rl.AttributionID IN (SELECT AttributionID FROM answers WHERE EmployeeID = tblEmp.EmployeeID)
ON attrib_division_rl.AttributionID = attrib_organization_rl.AttributionID
ON answers.AttributionID = attrib_division_rl.AttributionID

GROUP BY DivisionID, OrganizationID, EmployeeID

Options: ReplyQuote

Written By
Help - complex joins
May 27, 2020 01:44PM
June 02, 2020 10:00AM
June 02, 2020 08:03PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.