Recursive Join Problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Recursive Join Problem

Recursive Join Problem

Rate Topic

Display Mode

Topic Options

Author

Message

RAO-492486

Posted Saturday, March 14, 2009 2:11 AM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, June 02, 2013 11:59 PM
Points: 270, Visits: 279

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

Ramesh Saive

Posted Saturday, March 14, 2009 4:11 AM

SSCrazy

Group: General Forum Members
Last Login: Saturday, June 01, 2013 4:52 AM
Points: 2,556, Visits: 2,590

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

The Dixie Flatline

Posted Saturday, March 14, 2009 9:18 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:23 PM
Points: 3,836, Visits: 5,635

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

Jeff Moden

Posted Saturday, March 14, 2009 10:42 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 33,111, Visits: 27,037

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #675921

The Dixie Flatline

Posted Saturday, March 14, 2009 10:52 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:23 PM
Points: 3,836, Visits: 5,635

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