Synchronize a Select Projection
I would like to use a table alias within the projection of another select statement, like this:
SELECT [ID]
,[Name]
,CASE WHEN 'reptile' IN
(SELECT [Type] FROM dbo.Pets [S] INNER JOIN [P] ON S.People_ID = P.ID)
THEN 1 ELSE 0 END [HasReptile]
FROM People [P]
Within the projection there is a select statement, which is trying to use the table alias [P]. Evidentally this is not legal. How can I achi开发者_如何学Goeve my goal without using two separate queries?
In effect, my query is saying "show '1' for any person who owns a pet of type 'reptile'.
I am using SQL Server 2008.
SELECT [ID]
,[Name]
,CASE WHEN 'reptile' IN
(SELECT [Type] FROM dbo.Pets [S] WHERE S.People_ID = P.ID)
THEN 1 ELSE 0 END [HasReptile]
FROM People [P]
精彩评论