MySQL Forums
Forum List  »  General

Re: Help - complex joins
Posted by: Peter Brawley
Date: May 28, 2020 10:01AM

Your table names will be unfamiliar to most everyone else. Aliases exist to help readability & comprehension ...

SELECT
  AVG(a.Score) AS ScoreAvg, 
  COUNT(a.Score), 
  d.ID As DivisionID, 
  o.ID AS OrganizationID, 
  a.EmployeeID
FROM answers                     AS a
JOIN attrib_division_rl          AS d      USING(AttributionID)
LEFT JOIN attrib_organization_rl AS o      USING(AttributionID)    
LEFT JOIN employees              AS tblEmp USING(AttributionID,EmployeeID)
GROUP BY DivisionID, OrganizationID, EmployeeID;

... assuming by CNT() you mean COUNT() and simplifying the employee Join.

Group By a Left Joined column contradicts itself ... Group By Null isn't possible, so either that column ought not to be Grouped By, or the Join needs to be Inner.

And Group By across joins multiplies aggregate results as if the Sorcerer's Apprentice had done them; you really want to try to do the aggregation in one base or derived table and Join to other tables separately for associated info as necessary.

The query Groups By joined columns d.id and o.id, so make a derived table ...

  SELECT 
    d.ID As DivisionID, o.ID AS OrganizationID, 
    a.EmployeeID, a.Score
  FROM answers                AS a
  JOIN attrib_division_rl     AS d USING(AttributionID)
  JOIN attrib_organization_rl AS o USING(AttributionID)    
  JOIN employees              AS e USING(AttributionID,EmployeerID)

... and aggregate on that ...

SELECT
  DivisionID, OrganizationID, EmployeeID,
  AVG(a.Score) AS ScoreAvg, COUNT(a.Score) 
FROM (
  SELECT 
    d.ID As DivisionID, o.ID AS OrganizationID, 
    a.EmployeeID, a.Score
  FROM answers                AS a
  JOIN attrib_division_rl     AS d USING(AttributionID)
  JOIN attrib_organization_rl AS o USING(AttributionID)    
  JOIN employees              AS e USING(AttributionID,EmployeerID)
) x
GROUP BY DivisionID, OrganizationID, EmployeeID;

Options: ReplyQuote


Subject
Written By
Posted
May 27, 2020 01:44PM
Re: Help - complex joins
May 28, 2020 10:01AM
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.