Creating a dyanamic recursive 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

SQL Server Development (2000)

Creating a dyanamic recursive query

Author

Topic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2006-08-23 : 03:53:40

I'm building a crime statistics system which displays the levels of crime for an area.

Crime is recovered at the lowest (smallest) level. Each of these smaller levels is grouped into a larger level. This larger level can be grouped into a larger level again, and so on.

Like a Russian doll :)

When a LocationID is passed in, I need to retreieve a list of all the lowest level locations that exist within that Location.

Below is the code for the table:


CREATE TABLE [dbo].[Locations] (
	[LocationID] [int] IDENTITY (1, 1) NOT NULL ,
	[ParentLocationID] [int] NULL ,
	[LevelID] [int] NULL ,
	[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL 
)

Here is the test data:


LocationID  ParentLocationID LevelID     Name                                               
----------- ---------------- ----------- -------------------------------------------------- 
1           4                3           Nailsea
2           4                3           Portishead
3           4                3           Clevedon
4           5                2           Nailsea area
5           -1               1           North Somerset

So if I passed in LocationID: 1
It would just return 1

If I passed in LocationID: 4
It would return 1,2,3

If I passed in LocationID: 5
It would return 1,2,3

I'm developing this in MS SQL Server 2000, which doesn't appear to surport recursive queries.

Can anyone help me out?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-23 : 04:02:34

Have a look at this article, it will give you what you need:

http://www.sqlteam.com/item.asp?ItemID=8866

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 04:09:32

How come if you pass LocationID 4 to return {1,2,3} and when passing LocationID 5 you also want {1,2,3}?
Should not LocationID 5 return {4,1,2,3}, since when you pass LocationID 1 you want {1}?

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-23 : 08:03:37

You need to run all your database access via stored procedures.
SPs and functions support recursion.

Have you considered v2005 - it introduces common table expressions which support recursion and are introduced for just this sort of requirement.

Also do a search for Joe Celko about trees and hierarchies for some other ideas about how to represent your data.

Also see
http://www.nigelrivett.net/SQLTsql/RetrieveTreeHierarchy.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2006-08-23 : 08:14:21

Lumbago: Yes I can across that article before posting here, but I wasn't able to work out a solution after reading it.

Peso: This is because the crime data is only held at the lowest level.

So LocationID's 1,2,3 contain crime data. Whereas LocationID does not. So there were be no advantage to having it included.

nr: This is being designed as a SQL Server 2000 stored procedure. Unfortunately MS SQL Server 2005 is not available as an option. I wish it was, as I know it supports recusive procedures through CTE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources