Recursive Quries(Common Table Expressions)

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

General SQL Server Forums

New to SQL Server Programming

Recursive Quries(Common Table Expressions)

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2009-10-14 : 06:56:29

Dear All,
Please Check this Conditions

DECLARE @tbl1 AS TABLE(MasterId INT)
DECLARE @tbl2 AS TABLE(MasterId INT,ItemId INT,ItemStatus VARCHAR(10))
INSERT INTO @tbl1(MasterId) VALUES(1)
INSERT INTO @tbl1(MasterId) VALUES(2)
INSERT INTO @tbl1(MasterId) VALUES(3)
INSERT INTO @tbl1(MasterId) VALUES(4)
INSERT INTO @tbl1(MasterId) VALUES(5)

INSERT INTO @tbl2(MasterId,ItemId,ItemStatus) VALUES(1,100,'No')
INSERT INTO @tbl2(MasterId,ItemId,ItemStatus) VALUES(2,100,'No')
INSERT INTO @tbl2(MasterId,ItemId,ItemStatus) VALUES(2,3,'Master')
INSERT INTO @tbl2(MasterId,ItemId,ItemStatus) VALUES(2,4,'Master')
INSERT INTO @tbl2(MasterId,ItemId,ItemStatus) VALUES(4,2,'Master')

Using Recursive Queries (Common Table Expression) passing masterId as 2 i want to get the result
in Following Manner

MasterId, MasterParent
----------------------
2 NULL (ItemStatus as 'No')
3 2
4 2

Don't go for Infinite Loop

Its Very Urgent..............................

Regards
Riju A.O

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-14 : 07:58:19

[code]
; with rcte
as
(
select MasterId, MasterParent = NULL
from @tbl2
where MasterId = 2
and ItemStatus = 'No'

union all

select t.ItemId, r.MasterId
from rcte r
inner join @tbl2 t on r.MasterId = t.MasterId
where t.ItemStatus = 'Master'
)
select *
from rcte
[/code]

KH
[spoiler]Time is always against us[/spoiler]

aoriju
Posting Yak Master

156 Posts

Posted - 2009-10-14 : 08:06:19

Thanks for Your Reply.
Its Working.
One More Douut
When lat insert Query is Like This
INSERT INTO @tbl2(MasterId,ItemId,ItemStatus) VALUES(4,2,'Master')

Any Infinit Looping problem Comes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-14 : 08:15:15

why don't you give it a try . . .

KH
[spoiler]Time is always against us[/spoiler]

aoriju
Posting Yak Master

156 Posts

Posted - 2009-10-14 : 08:18:36

Ok..I will Try it...................

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-15 : 01:24:07