SQLTeam.com | Weblogs | Forums

Child & Parent Id


#1

Hi All,

I have a table where I am maintaining Menu Id and Parent Id. Is there a way to get all the menu_id list which are attached with any one menu id.

For Exmple if I write Select * from Menu Where Menu_Id='1.1.1.1'

I should get all the parent detail also, please suggest

MENU_ID MENU_NAME MENU_PARENT_ID
1 HRMS NULL
1.1 MASTERS 1
1.1.1 WORK STRUCTURE 1.1
1.1.1.1 LEVEL 1.1.1


#2

Try this:

with cte
  as (select *
        from yourtable
       where MENU_ID='1.1.1.1'
      union all
      select b.*
        from cte as a
             inner join yourtable as b
                     on b.MENU_ID=a.MENU_PARENT_ID
     )
select *
  from cte