Re: Help - complex joins

[Bad Programmer]

You'd like to see a schema. That's not easy, in part because I'm under somme semblance of proprietary/trade secrets restrictions.
Division table has 2 fields, RecordID smallint, Entry varchar
Organization table has RecordID, Name, SubName
Employees table has RecordID, EmployeeID, FName, MidInit, LName . . .
EmployeeID is a copy of RecordID to accommodate your USING

Attributions is a table that relates to division & organization according to ratings given 'attributed' to them by emmployees. Employees cause ratings and attributions are assigned to them via the attrib_ tables. Those tables' fields are RecordID, AttributionID & FieldID. FieldID is a foriegn key for RecordID in the division & organization table.
Ratings have Attributions (entities attributed to the rating) and Employees (the person who caused the rating). Answers are an array of 'ratings' that are recorded whenever an employee does something that causes the rating. WHEW! - You asked - lol.

Answers have a Score and an AttributionID and an EmployeeID.
There is a direct correlation between division & organization, AttributionID.
There is no direct correlation between employee & division or organization.
That is the problem.

I need the report to show all divisions with their average & count.
For each division I need to see the records that are 'No Organization' and then each of the organizations with AttributionIDs that match the division.
Under each organization I need to show any 'No Employee' records and then all the records for each employee having caused answers where the AttributionID matches both the division and the organization.

If there's any way to 'clean up the data' I'd love to learn of it.
You were right about me calling fields from employee instead of tblTier3 - fixed. Thanks.
I'm still getting the same error:1054 Unknown column 'AttributionID' in 'from clause'

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 INNER JOIN division AS tblTier1 ON attrib_division_rl.FieldID = tblTier1.RecordID) USING(AttributionID)
LEFT JOIN (attrib_organization_rl INNER JOIN organization AS tblTier2 ON attrib_organization_rl.FieldID = tblTier2.RecordID) USING(AttributionID)
LEFT JOIN employee AS tblTier3 USING(AttributionID, EmployeeID)
) x
GROUP BY Tier1ID, Tier2ID, Tier3ID
ORDER BY Tier1Val, Tier2Val, Tier3Val;