Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive Join Problem


Recursive Join Problem

Author
Message
RAO-492486
RAO-492486
SSC Veteran
SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 301
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
Ramesh Saive
Ramesh Saive
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2564 Visits: 2643
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
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6898
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
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44948 Visits: 39860
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6898
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
RAO-492486
RAO-492486
SSC Veteran
SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 301
Thanx Friends..
Goldie Lesser
Goldie Lesser
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 1482
RAO (3/16/2009)
Thanx Friends...... i found the solution....


Would you post it here for the rest of us?
RAO-492486
RAO-492486
SSC Veteran
SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 301
RAO
RAO-492486
RAO-492486
SSC Veteran
SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 301
Hi Friends ;
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search