Multiple CTE where clauses referece CTW column

user_1270958 0
Viewed 109

For example 

WITH UserDetail (UserId, UserName)
AS
(
    SELECT TOP(10) U.UserId,U.UserName
    FROM UserTable U        
),
UserAction (ActionName,ActionType)
AS
(
    SELECT TOP(10) A.ActionName,A.ActionType
    FROM ActionTable A
    WHERE A.UserId = UserDetail.UserId // Is it possible to direct reference  
)
WHERE A.UserId = UserDetail.UserId

Can I direct doing this instead of joining of UserDetail inside my second CTE.

I am getting error of:

multi-part of identifier "UserDetail.UserId" could not be found

In the CTE reference, is it possible to reference back to previous CTE without joining the CTE table? Or I am writing a wrong query

sql-server-2012 tsql sql-server sql
1 Answers

You can use like this - with joining the UserDetail cte 

WITH UserDetail (UserId, UserName)
AS
(
    SELECT TOP(10) U.UserId,U.UserName
    FROM UserTable U        
),
UserAction (ActionName,ActionType)
AS
(
    SELECT TOP(10) A.ActionName,A.ActionType
    FROM ActionTable A inner join UserDetail
    on A.UserId = UserDetail.UserId
)

OR you can use subquery

WITH UserDetail (UserId, UserName)
    AS
    (
        SELECT TOP(10) U.UserId,U.UserName
        FROM UserTable U        
    ),
    UserAction (ActionName,ActionType)
    AS
    (
        SELECT TOP(10) A.ActionName,A.ActionType
        FROM ActionTable A
        where A.UserId in (select UserDetail.UserId from UserDetail)
    )
user_69148640
Related