title: Using SELECT result in another SELECT
body: <p>So here is my query</p>
  <pre><code>SELECT 
      * 
  FROM 
      Score  AS NewScores 
  WHERE 
      InsertedDate &gt;= DATEADD(mm, -3, GETDATE());
  
  
  SELECT 
      ROW_NUMBER() OVER( ORDER BY NETT) AS Rank, 
      Name, 
      FlagImg, 
      Nett, 
      Rounds 
  FROM (
      SELECT 
          Members.FirstName + ' ' + Members.LastName AS Name, 
          CASE 
              WHEN MenuCountry.ImgURL IS NULL THEN 
                  '~/images/flags/ismygolf.png' 
              ELSE 
                  MenuCountry.ImgURL 
          END AS FlagImg, 
          AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett, 
          COUNT(Score.ScoreID) AS Rounds 
      FROM 
          Members 
          INNER JOIN 
          Score 
              ON Members.MemberID = Score.MemberID 
          LEFT OUTER JOIN MenuCountry 
              ON Members.Country = MenuCountry.ID 
      WHERE 
          Members.Status = 1 
      GROUP BY 
          Members.FirstName + ' ' + Members.LastName, 
          MenuCountry.ImgURL
      ) AS Dertbl 
  ORDER BY;
      
  </code></pre>
  <p>The query is to give a result set for a GridView based leaderboard and what I want is to only get the average of Scores that are less than 3 months old. I have this in 2 parts as you can see and obviously it gives an error like this.</p>
  <blockquote>
  <p>Msg 4104, Level 16, State 1, Line 2</p>
  <p>The multi-part identifier &quot;<code>NewScores.NetScore</code>&quot; could not be bound.</p>
  </blockquote>
  <p>Which is because of this <code>AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett</code></p>
  <p>How do I make it so that I can use <code>NewScores</code> there so I'm only getting the average of the scores less than 3 months old?</p>
  <p>EDIT: Using the answers people provided I've solved it by using a join in the correct place and here is the correct query:</p>
  <pre><code>SELECT ROW_NUMBER() OVER(ORDER BY NETT) AS Rank, Name, FlagImg, Nett, Rounds FROM (SELECT Members.FirstName + ' ' + Members.LastName AS Name, CASE WHEN MenuCountry.ImgURL IS NULL THEN '~/images/flags/ismygolf.png' ELSE MenuCountry.ImgURL END AS FlagImg, AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett, COUNT(NewScores.ScoreID) AS Rounds FROM Members INNER JOIN (SELECT * FROM Score WHERE InsertedDate &gt;= DATEADD(mm, -5, GETDATE())) NewScores ON Members.MemberID = NewScores.MemberID LEFT OUTER JOIN MenuCountry ON Members.Country = MenuCountry.ID WHERE Members.Status = 1 GROUP BY Members.FirstName + ' ' + Members.LastName, MenuCountry.ImgURL) AS Dertbl ORDER BY Nett ASC
  </code></pre>