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...
Be Happy!
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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
-
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
.
-
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...
-
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.
To Who...?
-
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 -
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 -
I personally think the both solutions performs ons and the same thing... I dont see any difference.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply