How to replicate nested from statement to nested with statement?

I have created a nested from statement that can be seen below:

SELECT Player.playerid, name as [Full Name], avgs as [Player Average], format(teamavg, 'C') as [Team Average],Player.teamid, format((Team.teamavg - Player.avgs), 'C') AS [Difference], yr as [Last Year]
        FROM (SELECT playerid, teamid, MAX(yearid) as yr, format(avg(salary), 'c') AS avgs
                     FROM Spring_2021_BaseBall.dbo.Salaries 
                     GROUP BY playerID, teamid) Player,
        (SELECT teamid, yearid, avg(salary) AS teamavg
                FROM Spring_2021_BaseBall.dbo.Salaries 
                GROUP BY teamid, yearid) Team,
        (SELECT playerid,
                CONCAT(nameGiven, ' (', nameFirst, ')', ' ', nameLast) as name 
                FROM Spring_2021_BaseBall.dbo.People ) Name
      WHERE Player.playerid = Name.playerid and 
            Player.teamid = Team.teamid and 
            Player.yr= Team.yearid
      ORDER BY Player.playerid ASC, yearid DESC

I am trying to recreate the same results with a nested with statement, I have attempted the below, but it results in a multi-part identifier could not be bound. How would I solve this issue because I thought I named each query correctly?

With Player (playerid, teamid, yearid, avgs)  as (SELECT playerid, teamid, MAX(yearid), format(avg(salary), 'C') as avgs 
         FROM Spring_2021_BaseBall.dbo.Salaries 
         GROUP BY playerid, teamid), 
     Team (teamid, yearid, teamavg) as (SELECT teamid, yearid, avg(salary) as teamavg
         FROM Spring_2021_BaseBall.dbo.Salaries 
         GROUP BY teamid, yearid), 
     FullName (playerid, name) as (SELECT playerid, CONCAT(nameGiven, ' (', nameFirst, ')', ' ', nameLast) as name 
         FROM Spring_2021_BaseBall.dbo.People)
Select Player.playerid, Player.teamid, Player.yearid, Player.avgs, Team.teamavg, FullName.name 
         FROM Spring_2021_BaseBall.dbo.Salaries, Spring_2021_BaseBall.dbo.People
         WHERE Player.playerid = FullName.playerid and 
            Player.teamid = Team.teamid and 
            Player.yr= Team.yearid
         ORDER BY Player.playerid ASC, yearid DESC
go

Answer

The last FROM statement is still referring to the original tables, not the ones that are defined with the WITH statement.

If you change it from

FROM Spring_2021_BaseBall.dbo.Salaries, Spring_2021_BaseBall.dbo.People

FROM Player, Team, FullName

should give you the expected result

Advertisement

Answer