CODEWITHSUNDEEP
sqlsql-server
Aleksander Chelpski
Aleksander Chelpski

Reputation: 771

CTE - get the parent

I know there were plenty of question of CTE but I still don't know how to do that. I have table Messages with columns: id and previousMessageId. How can I get the parent for id = 957 - it should be: 950.

table Messages
--------------
id  | previousMessageId
957 | 956
956 | 950
950 | NULL

This is my query:

WITH previous AS 
( 
    SELECT id  
    FROM Messages 
    WHERE id = 957 

    UNION ALL 

    SELECT cur.id 
    FROM Messages cur
    INNER JOIN previous ON cur.previousMessageID = previous.id
) 
SELECT * FROM previous

It gives me:

957
958

But the result should be: 950

Upvotes: 2

Views: 954

Answers (3)

Ben Thul
Ben Thul

Reputation: 32737

declare @table table(id int,   previousMessageId int)
insert into @table select 957 , 956
insert into @table select 956 , 950
insert into @table select 950 , NULL
insert into @table select 999 , 998
insert into @table select 998 , 997
insert into @table select 997 , NULL;

with cte as (
    -- base case - get all records with no parent
    select *, 
        cast(concat('/', id, '/') as varchar(max)) as [path],
        id as [root]
    from @table
    where previousMessageId is null

    union all

    -- recursive step
    select child.*, 
        concat(parent.path, child.id, '/'),
        parent.root
    from @table as child
    join cte as parent
        on parent.id = child.previousMessageId
)
select *
from cte
where id = 957;

The root column in the final output is the first in the line of ancestors that ends with that particular ID. As to how I accomplished it, the trick is essentially always carrying the parent.root forward through the recursion step.

Upvotes: 0

PSK
PSK

Reputation: 17953

You can try like following.

declare @table table(id int,   previousMessageId int)
insert into @table select 957 , 956
insert into @table select 956 , 950
insert into @table select 950 , NULL
insert into @table select 999 , 998
insert into @table select 998 , 997
insert into @table select 997 , NULL

;WITH previous 
     AS (SELECT id, 
                previousmessageid 
         FROM   @table 
         WHERE  id = 957
         UNION ALL 
         SELECT cur.id, 
                cur.previousmessageid 
         FROM   @table cur 
                INNER JOIN previous 
                        ON cur.id = previous.previousmessageid) 
SELECT ID 
FROM   previous 
WHERE  previousmessageid IS NULL

In above example, for Id 957 you will get 950 and for Id 999 you will get 997

Upvotes: 4

Dwight Reynoldson
Dwight Reynoldson

Reputation: 960

You can use a second column to denote the parent id. The parent layer will have a null column the child layer will then reference the parent layer.

WITH previous AS 
( 
SELECT id, null as parentid  
FROM Messages 
WHERE id = 957 

UNION ALL 

SELECT cur.id, cur.previousMessageID 
FROM Messages cur
INNER JOIN previous ON cur.previousMessageID = previous.id
) 
SELECT ParentID FROM previous where ParentID is not null

Upvotes: 0

Related Questions