#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0

    Referencing a derived value in an INNER JOIN?


    Hi there,

    I wonder if anyone can please advise me on how I might restructure this query so that I can use one of the derived values (average_score, as listed in line 3) further down inside the second INNER_JOIN statement?
    I suspect that it doesn't work because the INNER_JOIN parts gets calculated before the derived values get created and therefore they are NULL?

    SELECT e.index, e.name, e.occupation, e.location,
    COUNT(r.index) AS number_of_reports,
    ROUND(AVG(r.score)) AS average_score,
    ROUND(SUM(r.cost)/SUM(r.hours_worked)*60) AS hourly_rate,
    g.grade as grade
    FROM `employees` AS e
    INNER JOIN `reports` AS r
    ON e.index = r.host_index
    INNER JOIN `grades` as g
    ON (hourly_rate=g.rate)

    GROUP BY e.index, e.name, e.location
    HAVING COUNT(r.index) >= 2
    ORDER BY AVG(r.score) DESC

    Thanks for any help!
    Sean.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Code:
    SELECT data.*
         , g.grade
      FROM ( SELECT e.index
                  , e.name
                  , e.occupation
                  , e.location
                  , COUNT(r.index) AS number_of_reports
                  , ROUND(AVG(r.score)) AS average_score
                  , ROUND(SUM(r.cost)/SUM(r.hours_worked)*60) AS hourly_rate
               FROM `employees` AS e
             INNER 
               JOIN `reports` AS r
                 ON r.host_index = e.index
             GROUP 
                 BY e.index
                  , e.name
                  , e.occupation
                  , e.location
             HAVING COUNT(r.index) >= 2 ) AS data
    INNER 
      JOIN `grades` as g
        ON g.rate = data.hourly_rate
    ORDER 
        BY data.average_score DESC
    thread moved to mysql forum, because ms sql server doesn't use those horrendous backticks

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0
    Thank you :-)
    I'll give this a try tonight!

IMN logo majestic logo threadwatch logo seochat tools logo