MySQL Forums
Forum List  »  General

Re: Help - complex joins
Posted by: Bad Programmer
Date: May 29, 2020 11:25AM

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:
Tier1ID, Tier1Val, Tier2ID, Tier2Val, Tier3ID, Tier3Val,
AVG(Score) AS ScoreAvg, COUNT(Score) AS ScoreCnt
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;

Options: ReplyQuote

Written By
May 27, 2020 01:44PM
Re: Help - complex joins
May 29, 2020 11:25AM
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.