Re: Help - complex joins

[Bad Programmer]

Thanks very much for your response.
I apologize for that shoddy example code.
As you so aptly pointed out, my problem is with the AttributionID in one table and the EmployeeID in the other table.
What I need of course is the record to match AttributionID AND AttributionID AND EmployeeID

If I run it with just the attrib_ tables it works fine.
When I add the employee
I'm getting an error: "1054 Unknown column 'AttributionID' in 'from clause'"

My problem is that I can't specify both the AttributionID and the EmployeeID in the same join, which is what I need:
AVG(Score), COUNT(Score) where AttributionID matches division and matches organization and where EmployeeID matches employee

I really need the division to organization and the organization to employee to be left joins too.

Here is the actual updated sql:
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,
employee.EmployeeID AS Tier3ID, TRIM(CONCAT(employee.LName, ', ', IFNULL(employee.FName, ''), ' ', IFNULL(employee.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;