UNION ALL between two recursive WITH

  • DECLARE

    @user-id uniqueidentifier

    SET @user-id = 'EC3C39B4-0E80-4EE9-A235-364A17B3A92C'

    ----------------------

    --My First Recursive

    ----------------------

    WITH

    Arbo (UserId,UserName)as (

      SELECT UserId,UserName FROM aspnet_Users WHERE UserId = @user-id

      UNION ALL

      SELECT a.UserId,a.UserName FROM aspnet_Users a JOIN Arbo ar ON ar.UserId = a.UserIdManager

    )

    SELECT UserId,UserName FROM Arbo

    ----------------------

    --My Second Recursive

    ----------------------

    WITH

    Arbo2 (UserIdManager,UserName,UserIdManager)as(

     SELECT UserId,UserName,UserIdManager FROM aspnet_Users WHERE UserId = @user-id AND UserIdManager IS NOT NULL

     UNION ALL

    SELECT a.UserId,a.UserName,a.UserIdManager FROM aspnet_Users a JOIN Arbo2 ar ON ar.UserIdManager = a.UserId

    )

    SELECT UserIdManager,UserName FROM Arbo2

     

    -- But I would like to do UNION ALL between my two recursive

    SELECT

    UserIdManager,UserName FROM Arbo2

    UNION

    ALL SELECT UserId,UserName FROM Arbo

    Is

    it possible ?

    I have this

    message : Msg 319, Level 15, State 1, Line 7

    Incorrect syntax near the keyword

    'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    Msg 336

    , Level 15, State 1, Line 20

    Incorrect syntax near

    'Arbo2'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.


    Kindest Regards,

    degrem_m
    Degremont

  • 1. The message about the semicolon is part of the answer:

    <...>

    SET @user-id = 'EC3C39B4-0E80-4EE9-A235-364A17B3A92C';

    WITH Arbo (UserId,UserName)as (

       <...> )

    SELECT UserId,UserName FROM Arbo;

    ----------------------

    --My Second Recursive

    ----------------------

    WITH Arbo2 (UserIdManager,UserName,UserIdManager)as(

    <...>

    2. To perform the UNION, store the results of the first CTE in a table variable, and use it with the second CTE:

    DECLARE @Arbo table(UserID int, UserName varchar(30));

     WITH Arbo (UserId,UserName)as (

      SELECT UserId,UserName FROM aspnet_Users WHERE UserId = @user-id

      UNION ALL

      SELECT a.UserId,a.UserName FROM aspnet_Users a JOIN Arbo ar ON ar.UserId = a.UserIdManager

    )

    INSERT @Arbo(UserID, UserName)

    SELECT UserId,UserName FROM Arbo;

    --My Second Recursive

    WITH Arbo2 (UserIdManager,UserName,UserIdManager)as(

     SELECT UserId,UserName,UserIdManager FROM aspnet_Users WHERE UserId = @user-id AND UserIdManager IS NOT NULL

     UNION ALL

    SELECT a.UserId,a.UserName,a.UserIdManager FROM aspnet_Users a JOIN Arbo2 ar ON ar.UserIdManager = a.UserId

    )

    SELECT UserIdManager,UserName FROM Arbo2

    UNION

    SELECT UserID, UserName FROM @Arbo;

    Eddie Wuerch
    MCM: SQL

  • So good, Very thanks.


    Kindest Regards,

    degrem_m
    Degremont

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply