using Select in a From Clause

[Mdavis123]

I'm getting an error (invalid columnname 'ClientKey')when I check the syntax for this statement below. If i remove the Select in the From clause, every thing is ok except the wrong records show up. I need the most recent record from the Intake table.

Anybody with an idea on how to achieve this?
Thanks!

CREATE VIEW dbo.ClientDemographics
AS
SELECT
a.ClientKey AS 'Client ID',
a.Age AS Age,
a.CLIENT_Gender AS Gender,
a.RACE AS 'Ethnic Origin',
a.Marital_Status AS 'Marital Status'

FROM CLIENT AS a,
(Select top 1 Level_Education,Current_Employment
from Intake_Assess
order by intake_date desc) AS i
where a.ClientKey = i.ClientKey

 

[StevenK]

The reason your SQL is failing is because the field i.ClientKey does not exist - you need to include this in that part of the SELECT statement (within your FROM clause).
You are trying to create a link using a field that has not been defined for the alias 'i'.
Hope this helps.
Steve

 

[Mdavis123]

StevenK,
You were correct. Making the change in bold solved the issue.
Thank you for a timely response.
Mike

CREATE VIEW dbo.ClientDemographics
AS
SELECT
a.ClientKey AS 'Client ID',
a.Age AS Age,
a.CLIENT_Gender AS Gender,
a.RACE AS 'Ethnic Origin',
a.Marital_Status AS 'Marital Status',
i.Level_Education AS 'Edu Status',
i.Current_Employment AS 'Empl Status'
FROM CLIENT AS a,
(Select top 1 ClientKey, Level_Education,Current_Employment
from Intake_Assess
order by intake_date desc) AS i
where a.ClientKey = i.ClientKey