Looping in a query

Hello Everybody,

I have a requirement where the query needs to looped.

I have a table f553002A. In this table i have iykit,iyitm as columns.

for every iykit,there can be a set of iyitm

like this

select KIT, ITM from F553002A where KIT = 11758

KIT ITM

11758 11494

11758 11705

11758 11713

11758 11868

Again for every ITM as KIT in the same table ,there can have set of values(ITM) like this

select KIT, ITM from F553002A where KIT = 11713 (parent’s ITM)

KIT ITM

11713.0 2917.0
11713.0 2910.0
11713.0 5013.0
11713.0 2890.0
11713.0 2873.0
11713.0 2843.0
11713.0 2856.0
11713.0 2836.0
11713.0 2879.0
11713.0 7974.0
11713.0 2789.0
11713.0 2842.0
11713.0 2915.0
11713.0 2885.0
11713.0 2908.0
11713.0 2858.0
11713.0 2871.0
11713.0 2841.0

the same with 11494,11705,11868.

Again 2917.0 (ITM) in the above example as KIT can have another set of ITM's in the same table

I want all the END ITM's along with there Grand parent KIT and Immediate Parent Kit returned in a result set.

For Ex

11758 11713.0 2917.0

....

...

..

.

11758 11494 ...........................

........

...

..

.

However i know i can achieve this with Stored procdeure. I want this to be implemented in SQL Query where i can embeed it in a VBscript

Please help me in writing the query for this. Thanks in advance

We are using sql server 2000. not 2005

here you go..

Code Snippet

Create Table #f553002a (

[KIT] int ,

[ITM] int

);

Insert Into #f553002a Values(1,2);

Insert Into #f553002a Values(1,3);

Insert Into #f553002a Values(1,4);

Insert Into #f553002a Values(1,5);

Insert Into #f553002a Values(2,6);

Insert Into #f553002a Values(3,8);

Insert Into #f553002a Values(4,9);

Insert Into #f553002a Values(5,10);

Insert Into #f553002a Values(6,11);

Insert Into #f553002a Values(7,12);

Insert Into #f553002a Values(8,13);

Insert Into #f553002a Values(9,14);

Insert Into #f553002a Values(10,2);

Insert Into #f553002a Values(11,3);

;With CTE(KIT,ITM,Level)

as

(

Select KIT,ITM,1 Level from #f553002a Where KIT = 3 --@.StartKitValue

Union ALL

Select Child.KIT,Child.ITM,Main.Level + 1 From CTE Main

Join #f553002a Child On Main.Itm = Child.Kit

)

Select KIT,ITM From CTE Order By Level

|||

Can any body through a light why the query below is returning syntax error

With CTE(IYKIT,IYITM,Level)

as

(

Select IYKIT,IYITM,1 Level from f553002a Where IYKIT = 11758 --@.StartKitValue

Union ALL

Select Child.IYKIT,Child.IYITM,Main.Level + 1 From CTE Main

Join f553002a Child On Main.IYItm = Child.IYKit

)

Select IYKIT,IYITM From CTE Order By Level

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'With'.

I am running this on sql server 2005 Management studio.

How ever in my question,i requested for a solution which can work in SQL server 2000, but this works only in sql server 2005

|||

CTE only work with SQL Server 2005. You missed the semicolon ; before the WITH.

In 2000 you can use recursive sp call... let me try this..

sql