Recursive select Query

  • A--

    |

    |-B --

    | |-E

    | |-F

    | ---

    |-C--

    --- |-D

    Suppose this is my hierarchy of Employees. Employee D ReportsTo C and Employee E & F Reports To B. Employee B & C Reports to A.

    if i loggin as Employee A then i want to get Records of SalesReport of all the Employees Reports to A and the Employees Reports to his Subordinates and also their Subordinate. That is All records for the abouve given hierarchy.

    can I write the Recursive query for this is SQL

  • Yes. You will need to use recursive CTE's (Common Table Expressions).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • bhakti (5/8/2009)


    A--

    |

    |-B --

    | |-E

    | |-F

    | ---

    |-C--

    --- |-D

    Suppose this is my hierarchy of Employees. Employee D ReportsTo C and Employee E & F Reports To B. Employee B & C Reports to A.

    if i loggin as Employee A then i want to get Records of SalesReport of all the Employees Reports to A and the Employees Reports to his Subordinates and also their Subordinate. That is All records for the abouve given hierarchy.

    can I write the Recursive query for this is SQL

    Usually there are more than one ways of solving a given problem. In order that discussions are not reduced to mere theoretical concepts, please elaborate the underlying data structure a bit more so we can propose concrete solutions.

    I hope my comment will be taken in the right spirit.

    Regards

    Saurabh Dwivedy
    ___________________________________________________________

    My Blog: http://tinyurl.com/dwivedys

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537

    Be Happy!
  • CTE will do the job. But need more information about inputs and outputs. For reference, I am assuming the following data structure.

    CREATE TABLE #DestinationDept

    (

    [DestinationDeptID] [int] IDENTITY(1,1) NOT NULL,

    [SNO] [int] NOT NULL,

    [Desc] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ParentID] [int] NULL

    )

    Insert into #DestinationDept (SNO,[Desc],ParentID)

    Select 1,'A',NULL Union all

    Select 2,'B',1 Union all

    Select 3,'E',2 Union all

    Select 4,'F',2 Union all

    Select 5,'C',1 Union all

    Select 6,'D',5 Union All

    Select 7,'H',4

    --Select * from #DestinationDept

    ;with c as (Select DestinationDeptID,ParentID From #DestinationDept where ParentID = 2

    UNION ALL Select cs.DestinationDeptID,cs.ParentID From #DestinationDept as CS

    Inner Join c on c.DestinationDeptID = cs.ParentID

    )

    Select * from #DestinationDept As c2

    Where C2.DestinationDeptID in (Select c.DestinationDeptID from c)

    drop table #DestinationDept

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Hi Atif, in many ways I'm still a relative newbie to SQL Server... your example of a recursive CTE is probably an old technique to some of the regulars, but I'm sure glad I've found out about it!

    However, I was wondering if this is guaranteed to maintain the tree order as provided by Bhaki

    i.e.

    A

    |

    |=B

    | |=E

    | \=F

    |

    |=C

    \=D

    .

    Random Technical Stuff[/url]

  • The solution I provided might help. But, as said by other posters, the data structure is required. I posted on my assumption that;

    B is descendent of A

    E and F are Decendents of B

    C is decendent of A

    and

    D is decendent of C

    Now asuming this, I understood that if the OP queries B, he/she needs to get the complete hirarchy from B and its decendents and its decendents' decendents and so on....

    I hope the same thing was required...

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • See if this helps

    CREATE TABLE #Employees(Employee CHAR(1) NOT NULL PRIMARY KEY, ReportsTo CHAR(1))

    INSERT INTO #Employees(Employee, ReportsTo)

    SELECT 'A',NULL UNION ALL

    SELECT 'B','A' UNION ALL

    SELECT 'C','A' UNION ALL

    SELECT 'D','C' UNION ALL

    SELECT 'E','B' UNION ALL

    SELECT 'F','B'

    DECLARE @Start CHAR(1)

    SET @Start='A';

    WITH CTE AS

    (SELECT 0 AS Depth, Employee, ReportsTo, CAST(Employee AS VARCHAR(MAX)) AS FullPath

    FROM #Employees

    WHERE Employee=@Start

    UNION ALL

    SELECT C.Depth+1, A.Employee,A.ReportsTo, C.FullPath + '\' + CAST(A.Employee AS VARCHAR(MAX))

    FROM #Employees A

    INNER JOIN CTE C ON A.ReportsTo = C.Employee)

    SELECT Employee,ReportsTo

    FROM CTE

    ORDER BY FullPath

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Hey... I tried that. 🙂 Doesn't work if F reports to B.

    Random Technical Stuff[/url]

  • Hey... I tried that. Doesn't work if F reports to B.

    To Who...?

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Atif Sheikh (5/12/2009)


    Hey... I tried that. Doesn't work if F reports to B.

    To Who...?

    Er... sorry. Got that badly wrong. Doesn't work if B reports to F.

    Random Technical Stuff[/url]

  • ta.bu.shi.da.yu (5/13/2009)


    Atif Sheikh (5/12/2009)


    Hey... I tried that. Doesn't work if F reports to B.

    To Who...?

    Er... sorry. Got that badly wrong. Doesn't work if B reports to F.

    So who does F report to?

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • I guess all I'm saying is that you have an ORDER BY FullPath then it's sorting by the Employee's name, which will be in alphabetic order. Am I missing something in the logic?

    Random Technical Stuff[/url]

  • It will sort by the Employees name only within a level of the hierarchy. If you have a specific sorting requirement can you post it?

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Actually, I just reread the CTE and I totally misunderstood it. That's a very clever solution!

    I'm wondering if I can set a counter in place of your FULLPATH varchar(MAX)...

    I might try this tomorrow.

    Random Technical Stuff[/url]

  • I personally think the both solutions performs ons and the same thing... I dont see any difference.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

Viewing 15 posts - 1 through 14 (of 14 total)

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