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.

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