CTE unable to bind CTE table

[tshad]

I have the following:

WITH DistributionListCTE
AS
(
SELECT ParentID = NULL,ChildID = 1390,MemberTypeID = 4,Level = 0
UNION ALL
SELECT DistributionListID,MemberID,zdd.MemberTypeID,Level + 1
FROM DistributionList_Detail zdd
JOIN DistributionListCTE dcte on dcte.ChildID = zdd.DistributionListID
WHERE zdd.MemberTypeID in (2,4)
)
SELECT
convert(varchar,u.LastName) as Lastname
FROM DISTRIBUTIONLIST_DETAIL d
INNER JOIN vwSecurity_User u ON u.UserID = d.MemberID
WHERE d.DistributionListID in (Select ChildID From DistributionListCTE) AND
(DistributionListCTE.MemberTypeID = 1)

The error I get is:

The multi-part identifier "DistributionListCTE.MemberTypeID" could not be
bound.

Why does that not work???

I also tried:

WITH DistributionListCTE(ParentID, ChildID, MemberTypeID, Level)
AS
(
SELECT ParentID = NULL,ChildID = 1390,MemberTypeID = 4,Level = 0
UNION ALL
SELECT DistributionListID,MemberID,zdd.MemberTypeID,Level + 1
FROM DistributionList_Detail zdd
JOIN DistributionListCTE dcte on dcte.ChildID = zdd.DistributionListID
WHERE zdd.MemberTypeID in (2,4)
)
SELECT
convert(varchar,u.LastName) as Lastname
FROM DISTRIBUTIONLIST_DETAIL d
INNER JOIN vwSecurity_User u ON u.UserID = d.MemberID
WHERE d.DistributionListID in (Select ChildID From DistributionListCTE) AND
(DistributionListCTE.MemberTypeID = 1)

I still get the error..

If I change it to d.MemberTypeID it works fine.

Thanks,

Tom

 

[tshad]

Nevermind.

I figured it out.

I wasn't using the alias from the select statement. Origially, the JOIN
wasn't there so I didn't notice I was using an alias.

Thanks,

Tom