MySQL Forums
Forum List  »  General

Re: Help - complex joins
Posted by: Peter Brawley
Date: May 31, 2020 06:41PM

Why in the world do these tables use MyISAM?

What MySQL version are you running?

> The closest I've gotten was with my SELECT in
> the JOIN which you see in my original post.

Which aggregated across Joins thereby giving incorrect values.

Your DDL lets me fill in Join arguments presumptively ...

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

... it parses, but of course you should check them for accuracy. If the query gives the same result with and without Tier...Val columns in the Group By clause, you've demonstrated they can be done without (see my 1st reply above)

> I can give you access to the test database but I'd
> need an IP to grant permission to access the db server.

Except for the occasional appearance of MySQL staff, we're volunteers here. MySQL is open source, the basic idea is that everybody can learn and anybody can help. When direct db access is necessary, we've gone private, and that's when the meter turns on ... perhaps you'll want to postpone that till there's no alternative. A simpler & cheaper alternative for you is to add a bit of data---just enough rows of dummy data to generate a meaningful and perfectly predictable query result---to sample instances of the six tables in a dummy database, and post a mysqldump of that small dataset here.

Edited 1 time(s). Last edit at 05/31/2020 06:42PM by Peter Brawley.

Options: ReplyQuote

Written By
May 27, 2020 01:44PM
Re: Help - complex joins
May 31, 2020 06:41PM
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.