Looping and self joining to the same table in SQL

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)

Looping and self joining to the same table in SQL

Author

Topic

chaigh
Starting Member

1 Post

Posted - 2007-03-07 : 08:14:41

Currently I have a data warehouse process (Cognos) which is running in a SQLsever 2000 db. I have a "function" in Cognos which is currently retreiving data from a table and then selecting and retrieving more data from the same table depending on the value retrieved in prior select statement. The number of occurences is potential infinite. My routine works but due to the nature of the cognos function it is taking a lot of time to process. I wondered if its possible to program this logic into the driving source trans SQL select statement (I can't start using SQl procedures and only have the option of trans sql.)

Example below

Table A
Col 1 Col 2 Col 3
5 IED GBD
2 FRD IED
1 ZXX RRR
3 DED FRD

The theory is I start by selecting GBD on Col 3 and storing Col 1 value of 5. I then use Col 2 value and see if there are any matching records in Col 3 in the same table and repeat this process until I can't find any more records.

The result I would seak from the above is 10

The number of times I need to loop are not fixed.

I wonder if its possible to loop or something in a trans SQL ?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-07 : 11:07:25

Wouldn't the result from your example be 3?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-07 : 11:18:46

I believe this is kind of hierarchical recordset:

1 ZXX RRR
3 DED FRD
--->2 FRD IED
------->5 IED GBD

So 5+2+3 = 10 !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-07 : 12:21:35

Aha, I got the hierarchical part, I just didn't assume he was adding the numbers.

In SQL Server 2005 you could use a CTE (common table expression) to do this in a single query. In 2000 you'd have to use a loop so it probably wouldn't be much better than what you're seeing now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources