Using SELECT result in another SELECT

Question

So here is my query 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        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   Msg 4104  Level 16  State 1  Line 2 The multi-part identifier  quot NewScores NetScore quot  could not be bound   Which is because of this AVG CAST NewScores NetScore AS DECIMAL 18  4    AS Nett How do I make it so that I can use NewScores there so I m only getting the average of the scores less than 3 months old  EDIT  Using the answers people provided I ve solved it by using a join in the correct place and here is the correct query  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

User · Answer

What you are looking for is a query with WITH clause, if your dbms supports it. Then

WITH NewScores AS (
    SELECT * 
    FROM Score  
    WHERE InsertedDate >= DATEADD(mm, -3, GETDATE())
)
SELECT 
<and the rest of your query>
;

Note that there is no ; in the first half. HTH.

User · Answer

You are missing table NewScores  so it can t be found  Just join this table   If you really want to avoid joining it directly you can replace NewScores NetScore with   SELECT NetScore FROM NewScores WHERE  conditions on which they should be matched

User · Answer

NewScores is an alias to Scores table - it looks like you can combine the queries as follows   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 NewScores             ON Members MemberID   NewScores MemberID          LEFT OUTER JOIN MenuCountry              ON Members Country   MenuCountry ID      WHERE          Members Status   1          AND NewScores InsertedDate  gt   DATEADD mm  -3  GETDATE        GROUP BY          Members FirstName         Members LastName           MenuCountry ImgURL       AS Dertbl  ORDER BY

[sql] Using SELECT result in another SELECT

Examples related to sql

Examples related to select