Recursive Join Problem

Question

Recursive Join Problem

RAO-492486

Old Hand

Points: 396
More actions
March 14, 2009 at 2:11 am

#200965

IDParentidName
1NULLSearch
2NULLWS
3NULLEnter
4NULLArts
51Name1
106Name2
258Name3
268Name4
319Name4
3210Name5
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

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

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? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

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? Everybody look what's going down. -- Stephen Stills