asked on

Backward recursive query using CTE

Hi I have one table called cllquestion. in that i have got question which have parent question.

I cna create the hirearcy using CTE like child questions for a parent question. But i want it backwards like i have child question and i want all ancestor question for it.

Could you pelase give me the code that how i can do this.

Regards
Nitesha

anilallewar

There is nothing you can do in plain SQl; but definitely you can write recursive T-SQL functions for the same.

Here are some useful links

http://articles.techrepublic.com.com/5100-10878_11-5700193.html
http://www.webinade.com/web-development/creating-recursive-sql-calls-for-tables-with-parent-child-relationships
http://weblogs.sqlteam.com/jeffs/archive/2003/11/21/588.aspx

Thomasian

You just need to invert the fields when joining the table to the cte.

DECLARE @t table (id int, parent int)

INSERT INTO @t VALUES (1,NULL)
INSERT INTO @t VALUES (2,1)
INSERT INTO @t VALUES (3,1)
INSERT INTO @t VALUES (4,2)
INSERT INTO @t VALUES (5,3)
INSERT INTO @t VALUES (6,4)

--To get the children of a given parentid
DECLARE @parentid int
SET @parentid = 2
;WITH CTE (id, parent, level) AS
(
SELECT id, parent, 0 FROM @t WHERE id=@parentid 
UNION ALL
SELECT T1.id, T1.parent, level + 1
FROM @t T1 INNER JOIN CTE T2 ON T1.parent = T2.id 
)
SELECT * FROM CTE


--To get the ancestors of a given childid
DECLARE @childid int
SET @childid = 6
;WITH CTE (id, parent, level) AS
(
SELECT id, parent, 0 FROM @t WHERE id=@childid 
UNION ALL
SELECT T1.id, T1.parent, level + 1
FROM @t T1 INNER JOIN CTE T2 ON T1.id = T2.parent
)
SELECT * FROM CTE

Open in new window

Redshelf

ASKER

Please check the folllowing code.

if i use this procedure then i can get all t he child question for the question id(@id).
bu i want the other way round. like i want all parent questioins for the @ID .

if table is
id parentquestionid
1 0
2 1
3 1
4 2
5 2
6 3
7 3
8 5
9 8

so @id=5 then result shoul be
5 2
2 1
1 0

following code gives me all the child question so id @id=5 then result is.
5 2
8 5
9 8

any help will be appreciated.

N

ALTER PROCEDURE [dbo].[sp_getparentlist] (@id int)

AS

           ;WITH parentlist(id,text,parentquestionid,lastquestion,priority,level,Depth)

            AS

            (

              SELECT id,text,parentquestionid,lastquestion,priority,level,0 
              FROM callquestions
where id=@id
              Union All
              SELECT E.ID, E.text,e.parentquestionid, E.lastquestion, E.priority, E.level, M.Depth-1
                 
            FROM callquestions AS E
                        JOIN parentlist AS M
                          ON E.parentquestionid = M.id 
            )

            SELECT         id,text,parentquestionid,lastquestion,priority,level,Depth

            FROM parentlist

Open in new window

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Redshelf

ASKER

thank you Thomasian .

It works fine now.