I have created a nested from statement that can be seen below:
x
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
Advertisement
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
to
FROM Player, Team, FullName
should give you the expected result