CODEWITHSUNDEEP
sqlsql-serversql-server-2012hierarchical-datarecursive-query
Badhon Jain
Badhon Jain

Reputation: 1007

How to approach to find parents for child in sql server 2012?

I've a table like this:

enter image description here

If I'm given a single or a list of menuID, I need to find the all the parents for all the menuID. For Example if I'm given MenuID as 1601120013, I need to find the following.

enter image description here

I'm a newbie to sql, I'm not even sure how to approach this kind of scenario.

Upvotes: 0

Views: 48

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

You can do this using Recursive CTE

WITH cte 
     AS (SELECT menuld, 
                menutitle, 
                parentmenuld 
         FROM   Yourtable 
         WHERE  menuld = 1601120013 
         UNION ALL 
         SELECT t.menuld, 
                t.menutitle, 
                t.parentmenuld 
         FROM   cte c 
                INNER JOIN Yourtable t 
                        ON t.menuld = c.parentmenuld) 
SELECT * 
FROM   cte

Upvotes: 1

Related Questions