Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Recursive select Query Expand / Collapse
Author
Message
Posted Friday, May 08, 2009 11:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 12, 2010 3:15 AM
Points: 66, Visits: 78
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
Post #713469
Posted Saturday, May 09, 2009 1:02 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Yes. You will need to use recursive CTE's (Common Table Expressions).

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #713476
Posted Sunday, May 10, 2009 3:51 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 05, 2013 11:51 PM
Points: 488, Visits: 336
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!
Post #713684
Posted Sunday, May 10, 2009 11:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987
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 here

Post #713844
Posted Monday, May 11, 2009 7:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 03, 2011 7:09 AM
Points: 258, Visits: 494
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
Post #714071
Posted Tuesday, May 12, 2009 5:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987
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 here

Post #714890
Posted Tuesday, May 12, 2009 6:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #714924
Posted Tuesday, May 12, 2009 8:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 03, 2011 7:09 AM
Points: 258, Visits: 494
Hey... I tried that. Doesn't work if F reports to B.

Random Technical Stuff
Post #715092
Posted Tuesday, May 12, 2009 9:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987

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 here

Post #715622
Posted Wednesday, May 13, 2009 3:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 03, 2011 7:09 AM
Points: 258, Visits: 494
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
Post #715771
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse