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

Recursive Join Problem Expand / Collapse
Author
Message
Posted Saturday, March 14, 2009 2:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 17, 2013 12:25 AM
Points: 270, Visits: 298
ID Parentid Name
1 NULL Search
2 NULL WS
3 NULL Enter
4 NULL Arts
5 1 Name1
10 6 Name2
25 8 Name3
26 8 Name4
31 9 Name4
32 10 Name5

Hi All

I have implemented the recursive CTES for getting the data in Parent-Child relationship.

But my requirement is that i have get the details about the parentid's along with the recursive data' in a single join
When I give the Id

When i Select with the ID,Both Child and Parent should be displyed in join

Thanx
RAO
Post #675808
Posted Saturday, March 14, 2009 4:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:52 AM
Points: 2,551, Visits: 2,594
Since you already implemented the parent-child recursion using CTE, you can post that query along with some information about the output required for the given input. You mentioned that you want parent details as well in the recursive CTE, so I think you just need to add parent details in the anchor & recursive part of the CTE query.

This is an example of how to get parent information along with child using CTE. Note that this query is not tested (even for syntax), as currently I don't have any SQL at my machine.

;WITH EmployeeCTE
AS
(
SELECT EmployeeID, EmployeeName, NULL AS ManagerID, NULL AS ManagerName, 0 AS EmployeeLevel
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT E.EmployeeID, E.EmployeeName, C.EmployeeID, C.EmployeeName, C.EmployeeLevel + 1
FROM EmployeeCTE C
INNER JOIN Employees E ON C.EmployeeID = E.ManagerID
)
SELECT * FROM EmployeeCTE ORDER BY EmployeeID, EmployeeLevel


--Ramesh

Post #675827
Posted Saturday, March 14, 2009 9:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
my requirement is that i have get the details about the parentid's along with the recursive data' in a single join


Test question?

I've been taking buisness requirements for years, and never once has "in a single join" been specified as a requirement. So who made it mandatory for you?


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #675900
Posted Saturday, March 14, 2009 10:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
Bob Hovious (3/14/2009)
my requirement is that i have get the details about the parentid's along with the recursive data' in a single join


Test question?

I've been taking buisness requirements for years, and never once has "in a single join" been specified as a requirement. So who made it mandatory for you?


Heh... I've found that most folks that want it in a "single join" or "single query" are doing it because they intend to embed the code in a GUI rather than writing it in a stored proc. That certainly doesn't make it right, though.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #675921
Posted Saturday, March 14, 2009 10:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
I guess I'm lucky that my guys just ask me for stored procedures now. They know I will give them something that runs fast on the front end.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #675923
Posted Monday, March 16, 2009 6:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 17, 2013 12:25 AM
Points: 270, Visits: 298
Thanx Friends..
Post #676416
Posted Tuesday, March 17, 2009 11:02 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 1:09 PM
Points: 478, Visits: 1,417
RAO (3/16/2009)
Thanx Friends...... i found the solution....


Would you post it here for the rest of us?
Post #677702
Posted Wednesday, March 18, 2009 6:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 17, 2013 12:25 AM
Points: 270, Visits: 298

RAO
Post #678329
Posted Thursday, March 19, 2009 7:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 17, 2013 12:25 AM
Points: 270, Visits: 298
Hi Friends ;

Post #679391
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse