0

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.

Badhon Jain
  • 938
  • 6
  • 20
  • 38

1 Answers1

1

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
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Perfect. But I would like to understand more about CTE, I googled for it, can you please recommend something? Thanks. – Badhon Jain Jan 18 '16 at 02:55
  • 1
    Check this question [Sql server CTE and recursion example](http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example) – Pரதீப் Jan 18 '16 at 04:15