Post reply

Recusive query using cte

  • December 27, 2011 at 11:21 am

    #250472

    Please help me in this.. i have written cte but facing problem in achieving my desired output:

    organizationtable:

    oid, orgname, level, parentid

    10-unit1-1-0

    11-dept1-2-10

    12-dept2-2-10

    13-sec1-3-11

    empdtls:

    eid staffno oid

    1-99-13

    cte which i have writted is below:

    with testorg as(

    select oid,orgname,parentid from #organizationdtls where parentid = 0

    union all

    select a.oid,a.orgname,a.parentid from #organizationdtls a inner join testorg t on(a.parentid = t.oid)

    )

    select t.* from testorg t

    Help me in achieving such output if i pass oid = 10 then it should return

    empdtls:

    eid staffno oid

    1-99-13

    Please help me !!

  • December 27, 2011 at 11:26 am

    #1425812

    Cross post - please answer here: http://qa.sqlservercentral.com/Forums/Topic1227037-392-1.aspx

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply