Help needed regarding a looping query

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 2000 Forums

Transact-SQL (2000)

Help needed regarding a looping query

Author

Topic

Nightfall
Starting Member

3 Posts

Posted - 2007-07-13 : 04:58:57

Hello all,

I'm new to this forum (which looks great by the way!) and I have a question on how to build a query.

Let's say I have a query that returns these :

(id,name,parentid,levelintree)

Sample data :

id name parentid levelintree
----------------------------------
10 a 5 2
13 b 4 3
5 c 0 1
4 d 2 2
50 f 30 4

My objective is to finally have that :

id name parentid levelintree idofFirstlevel
--------------------------------------------------
10 a 5 2 5
13 b 4 3 2
5 c 0 1 5
4 d 2 2 1
50 e 30 4 8
2 f 1 1 2

An extra column with the id at level 1. In tblTree I have all ids and parentids.

My approach is to create a temporary table having 2 columns (originalId, FirstlevelID) and inner join with my query.
First I will insert all level 1 rows (since the firstlevelid will be equal to id) and then for the rest I have to somehow loop using id and parentid and then include them in the temp tbl.

Any ideas on how to do the looping ?

Many thanks in advance !

Alex

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 05:14:01

What do you have this far? Maybe we can help you figuring out where you went wrong.

Peter Larsson
Helsingborg, Sweden

Nightfall
Starting Member

3 Posts

Posted - 2007-07-13 : 05:28:11

Not much. I have created the temp table. Let's say the temp table is tblTemp. My query with all the rows is called Query1 and the table with all the ids tblTree. I have included all level 1 rows in the temp table and i have to loop through all the level > 1 rows.
So I guess that I have to take every row from my query (and not allready included in the temp table) and by using the id,parent id of the tblTree somehow loop to level 1 id.
Unfortunately I'm not that familiar with looping in tSQL because I usually do most of the hard job througv vb (only this time I can't :D ).

Thanks for your very prompt response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources