Using SELECT result in another SELECT

Question

So here is my query

SELECT 
    * 
FROM 
    Score  AS NewScores 
WHERE 
    InsertedDate >= 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 "NewScores.NetScore" 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 >= 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

I can accomplish the same thing by adding to the WHERE clause near the end with the date condition but I'd also like to know how to do this with the select alias. — Pandepic, Apr 12 '13 at 00:58
you have not joined table `NewScores` that's why column `NetScore` cannot be located. — John Woo, Apr 12 '13 at 00:59
Yeah I knew that part, I'm trying to work out how exactly to join it in this query so that it will work the way I want it to, the joins I tried so far failed. — Pandepic, Apr 12 '13 at 01:01
Possible duplicate of [Performing a query on a result from another query?](http://stackoverflow.com/questions/949465/performing-a-query-on-a-result-from-another-query) — Nissa, Nov 28 '16 at 16:09

score 60 · Accepted Answer · answered Apr 12 '13 at 01:08

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 >= DATEADD(mm, -3, GETDATE())
    GROUP BY 
        Members.FirstName + ' ' + Members.LastName, 
        MenuCountry.ImgURL
    ) AS Dertbl 
ORDER BY;

Thanks for showing me the correct place to do the join, I've solved the problem now. — Pandepic, Apr 12 '13 at 01:19

score 23 · Answer 2 · answered Apr 12 '13 at 01:16

23

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.

answered Apr 12 '13 at 01:16

Dinesh

4,437
5
40
77

1

What you call WITH-clause is actually called a "Common Table Expression". It's behave a bit like a temp table only available during the below select. It's not an answer to the question, but I do agree that this would be the best solution for the OP's situation. – T_D Feb 05 '16 at 12:52

score 1 · Answer 3 · answered Apr 12 '13 at 01:01

1

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}

answered Apr 12 '13 at 01:01

sashkello

17,306
24
81
109

Using SELECT result in another SELECT

3 Answers3

Linked

Related