Recursive Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursive Query

Author

Topic

mihir.mone
Starting Member

17 Posts

Posted - 2008-12-18 : 00:19:44

Hello,

I want to know how can I build a recursive SQL query.

I mean, I have a table in which I want to look for Parent-Child relation.
e.g.
TableX
id name ownerid

In this table, some rows may have values under 'ownerid' and some may not have. The one which may have value in 'ownerid' field have Owner present.

I want to traverse through the same table till the root(owner) of a particular ID given.

Hope somebody will help me.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 00:25:49

U can Use Common Table Expression Which gives parent-child hierarchy

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 00:28:52

WITH cte(id,ownerid)
(
SELECT id,ownerid FROM yourtable WHERE ownerid IS NULL

UNION ALL

SELECT y.id,y.ownerid FROM yourtable y
INNER JOIN cte ON (cte.id = y.ownerid)

)

Jai Krishna

mihir.mone
Starting Member

17 Posts

Posted - 2008-12-18 : 00:43:39

Dude,

Its great!!

Thanks for your help ! ;)

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 01:13:12

Welcome

Jai Krishna

mihir.mone
Starting Member

17 Posts

Posted - 2008-12-18 : 01:44:17

Above query gives me a result set with Union of all records in a hierarchy.

but, now
I have following values in table..

ID Name OwnerID

1 Mihir NULL
2 Amit 1
3 Naresh 2
4 Anil 3
5 Test NULL

If I pass 4 as ID, it should provide me its ancestor i.e. 1 in this case.

Is it possible?

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 01:55:36

For the above u can use this query

WITH cte(id,ownerid)
AS
(

SELECT id,ownerid FROM yourtable WHERE id =4

UNION ALL

SELECT y.id,y.ownerid FROM yourtable y
INNER JOIN cte ON (cte.ownerid = y.id)

)
SELECT id AS 'ancestor' FROM cte WHERE ownerid IS NULL

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 01:56:11

[code]
CREATE PROC GetAncestors
@ID int
AS
;With CTE (ID, Name, OwnerID) AS
(SELECT ID, Name, OwnerID
FROM Table
WHERE ID=@ID
UNION ALL
SELECT t.ID, t.Name, t.OwnerID
FROM CTE c
INNER JOIN Table t
ON t.ID=c.OwnerID
)
SELECT * FROM CTE
OPTION (MAXRECURSION 0)
GO

then execute it like below
EXEC GetAncestors 4
[/code]

mihir.mone
Starting Member

17 Posts

Posted - 2008-12-18 : 02:30:15

Above Query results in only 1 row which is -

ID Name OwnerID
4 Anil 3

I want it should return

ID Name OwnerID
1 Mihir NULL

Relation under consideration -

Child Parent
4 3
3 2
2 1
1 -

Is it possible?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 02:39:07

Yes.
Please feel free to play around with the suggestions given to you.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 02:39:40

quote:
Originally posted by mihir.mone

Above Query results in only 1 row which is -

ID Name OwnerID
4 Anil 3

I want it should return

ID Name OwnerID
1 Mihir NULL

Relation under consideration -

Child Parent
4 3
3 2
2 1
1 -

Is it possible?

were you telling about my suggestion?

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 03:30:29

If u want
Child Parent
4 3
3 2
2 1
1 -

Try This

WITH cte(id,ownerid)
AS
(

SELECT id,ownerid FROM yourtable WHERE id =4

UNION ALL

SELECT y.id,y.ownerid FROM yourtable y
INNER JOIN cte ON (cte.ownerid = y.id)

)
SELECT * FROM cte

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 03:33:20

quote:
Originally posted by Jai Krishna

If u want
Child Parent
4 3
3 2
2 1
1 -

Try This

WITH cte(id,ownerid)
AS
(

SELECT id,ownerid FROM yourtable WHERE id =4

UNION ALL

SELECT y.id,y.ownerid FROM yourtable y
INNER JOIN cte ON (cte.ownerid = y.id)

)
SELECT * FROM cte

Jai Krishna

same as what i posted on 12/18/2008 : 01:56:11

mihir.mone
Starting Member

17 Posts

Posted - 2008-12-18 : 03:49:34

yes visakh16,

It returned only one row from your query..

but I want a real root of the leaf provided (e.g. If provided 4(ID-leaf), I should get 1(ID-root))

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 04:06:41

Try This once u will get

WITH cte(id,ownerid)
AS
(

SELECT id,ownerid FROM yourtable WHERE id =4

UNION ALL

SELECT y.id,y.ownerid FROM yourtable y
INNER JOIN cte ON (cte.ownerid = y.id)

)
SELECT id AS 'ancestor' FROM cte WHERE ownerid IS NULL

Jai Krishna

mihir.mone
Starting Member

17 Posts

Posted - 2008-12-18 : 04:19:50

Jai Krishna,

This works for me. Great Deal!!

Can you please explain how it works?

I'm not good enough in SQL, I wrote a recursion in C# (;)) to achieve this.

You saved my call.

Thanks Dude

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 04:49:04

First it executes SELECT id,ownerid FROM yourtable WHERE id =4

o/p is 4,3

Now it checks for parent(owner) of 4
o/p is 3,2

Like this the recursive member(second query ) is executed until it doesnt return any thing

Jai Krishna

mihir.mone
Starting Member

17 Posts

Posted - 2008-12-18 : 04:52:56

Thanks

Cheers,
Mihir.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 04:54:29

Welcome

Jai Krishna

mihir.mone
Starting Member

17 Posts

Posted - 2008-12-22 : 10:30:58

Hello,

I executed above mentioned query where I used some other field insted of Index field and there are multiple records with same value.

It is giving me a list where I need only one value.

What should I do in this case?

Can anyone help me?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-22 : 10:44:09

Your final looks like:
SELECT ... FROM cte WHERE ...
you can use:
SELECT DISTINCT ... FROM cte WHERE ...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources