none
Give child data, select set of parent records with child records specific values RRS feed

  • Question

  • CREATE TABLE #temp (id varchar(10),menuname varchar(50),parent varchar(10) )
    insert into #temp values ('1','MenuGroup','0')
    insert into #temp  values  ('2','File','1')
    insert into #temp  values ('3','Master','1')
    insert into #temp  values ('4','Change Password','2')
    insert into #temp  values ('5','View options','2')
    insert into #temp  values ('6','Default Setting','2')
    insert into #temp  values ('7','options1','5')
    insert into #temp  values ('8','options2','5')
    insert into #temp  values ('9','options3','5')
    insert into #temp  values ('10','usermgt','3')
    insert into #temp  values ('11','groupmgt','3')
    insert into #temp  values ('12','Employee','3')
    insert into #temp  values ('13','Formsetting','11')
    insert into #temp  values ('14','menuseting','11')

    --this selected form only 
    CREATE TABLE #temp2 (id varchar(10),menuname varchar(50))
    insert into #temp2  values ('6','Default Setting')
    insert into #temp  values ('13','Formsetting')
    insert into #temp  values ('14','menuseting')

    --Result
    -----------------------------------------------
    --id   menuname            parent
    -----------------------------------------------
    --1    MenuGroup            0
    --3    Master               1 
    --11   groupmgt             3
    --13   Formsetting          11
    --14   menuseting           11
    --2    File                 1
    --6    Default Setting      2
    • Edited by powsul Tuesday, June 30, 2020 5:14 AM
    Tuesday, June 30, 2020 5:13 AM

All replies

  • Can you explain what is the logic behind the scene? Always state what version you are using...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 30, 2020 5:20 AM
    Answerer
  • Hi powsul,
     Can be solved by recursive cte. Please refer to:
     with cte1 
     as(select id,menuname,parent from #temp 
        where menuname='Default Setting'
        union all
        select a.id,a.menuname,a.parent from #temp a
        join cte1 on a.id=cte1.parent)
    
        ,cte2 as(select id,menuname,parent 
        from #temp
        where menuname='Formsetting'
        union all
        select b.id,b.menuname,b.parent from #temp b
        join cte2 on b.id=cte2.parent)
    
        ,cte3 as(select id,menuname,parent 
        from #temp
        where menuname='menuseting'
        union all
        select c.id,c.menuname,c.parent from #temp c
        join cte3 on c.id=cte3.parent)
    select * from cte1 
    union 
    select * from cte2
    union
    select * from cte3

    The result is as follows

    /*   
    1 MenuGroup 0
    11 groupmgt 3
    13 Formsetting 11
    14 menuseting 11
    2 File 1
    3 Master 1
    6 Default Setting 2
    */

     Best Regards,
     Echo

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    • Edited by Echo Liuz Tuesday, June 30, 2020 9:31 AM
    • Proposed as answer by Echo Liuz Thursday, July 2, 2020 1:23 AM
    Tuesday, June 30, 2020 9:28 AM
  • Check this approach too:

    ;
    with Q as
    (
       select t2.id, t2.menuname, t1.parent as parent
       from #temp2 as t2
       inner join #temp as t1 on t1.id = t2.id
      union all
       select t1.id, t1.menuname, t1.parent
       from #temp as t1
       inner join Q on Q.parent = t1.id
    )
    select distinct * from Q
    

    Tuesday, June 30, 2020 11:41 AM
  • with ect(id,mname,p)as 
    (select t1.id,t1.menuname,t1.parent from tbltemp t1
    inner join tbltemp2 t2 on t1.id = t2.id
    ),
    ect1(id,mname,p)as 
    (select t1.id,t1.menuname,t1.parent from tbltemp t1
    inner join ect t2 on t1.id = t2.p )
    
    
    
    
    select DISTINCT e1.id,e1.mname,e2.mname from ect e1
    inner join ect1 e2 on e1.p =  e2.id

    Tuesday, June 30, 2020 5:49 PM
  • Hi powsul,

    anything update?have you solved the issue?

     Best Regards,
     Echo


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 2, 2020 1:35 AM

  • Hi powsul,
    Has your problem been solved? If it is solved, please mark the point that you 
    think is correct as an answer. This can help others who encounter similar problems.

    Best Regards
    Echo

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    3 hours 15 minutes ago