SQL Clone
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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4174 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
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7012 Visits: 6902
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 Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114723 Visits: 41394
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7012 Visits: 6902
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 Eights!
SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)

Group: General Forum Members
Points: 990 Visits: 1501
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