How can I access an attribute in a subquery using the WHERE clause of an outer query?
How can I filter via an attribute that exists in a subquery using the WHERE clause of an outer query using TSQL / SQL Server 2008? The query was pretty long so I only included some of the code. I am getting an invalid column name for Sub.MemberStatusID=1. I am 100% sure that MemberStatusID exists in the table I am referencing.
SELECT
S.StateName AS StateName,
A.StateId AS StateId,
C.CountyName AS CountyName
FROM
(SELECT
M.MemberID AS MMemberID,
COALESCE(M.Number, R.Number) AS MyId
FROM
MTable AS M WITH(READUNCOMMITTED) FULL OUTER JOIN
RTable AS R WITH(READUNCOMMITTED) ON M.MNumber=R.MNumber) AS Sub
LEFT JOIN MOTable AS MO WITH(READUNCOMMITTED) ON Sub.MMemberID=MO.MemberID
WHERE
Sub.MemberStatusID=1 -- this is where the problem is at
....;
SELECT
S.StateName AS StateName,
A.StateId AS StateId,
C.CountyName AS CountyName
FROM
(SELECT
M.MemberID AS MMemberID,
COALESCE(M.Number, R.Number) AS MyId
FROM
MTable AS M WITH(READUNCOMMITTED) FULL OUTER JOIN
RTable AS R WITH(READUNCOMMITTED) ON M.MNumber=R.MNumber) AS Sub
LEFT JOIN MOTable AS MO WITH(READUNCOMMITTED) ON Sub.MMemberID=MO.MemberID
WHERE
Sub.MemberStatusID=1 -- this is where the problem is at
....;
ASKER
ewangoya, thanks, but I'm not looking to filter MemberStatusID using the inner WHERE clause. Can it be done using the WHERE clause of the outer query? If not, then I will use the inner WHERE clause. But I first want to know whether I can filter using the outer WHERE clause or not. Is it possible? If yes, please show me how! And if not, why can't this be done using the inner WHERE clause? Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks cmangus! I was missing the fact that I needed the attribute in both the inner and outer queries!
ASKER
Thanks cmangus!
SELECT
S.StateName AS StateName,
A.StateId AS StateId,
C.CountyName AS CountyName
FROM
(SELECT
M.MemberID AS MMemberID,
COALESCE(M.Number, R.Number) AS MyId
FROM MTable AS M WITH(READUNCOMMITTED)
FULL OUTER JOIN RTable AS R WITH(READUNCOMMITTED) ON M.MNumber=R.MNumber
WHERE MemberStatusID=1) AS Sub
LEFT JOIN MOTable AS MO WITH(READUNCOMMITTED) ON Sub.MMemberID=MO.MemberID