RECURSIVE SELECT - SQL Server Forums

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

Transact-SQL (2005)

RECURSIVE SELECT

Author

Topic

mima
Starting Member

4 Posts

Posted - 2007-11-01 : 05:18:41

Hi,

I’m having following table structure

Table Locations
ID ParentID
1 0
2 1
3 2
4 3
5 4

Table Properties
LocationID Location
1 Europe
2 Western Europe
3 Germany
4 Bavaria
5 Berlin

Represented data in this case means that I have location Europe with its child location Western Europe with child location Germany with child location Bavaria with child location Berlin.
Europe > Western Europe > Germany > Bavaria > Berlin

Now I need SQL statement to perform search on following way:
For example if I search for Western Europe it needs to return not just Western Europe but its child’s to, in this case Germany, Bavaria and Berlin.

Any help appreciated,
Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 05:23:30

see http://mindsdoor.net/SQLTsql/RetrieveTreeHierarchy.html

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

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-11-01 : 09:05:21

In 2005, he should use the new Common Table Expressions for recursive queries. Plus, mima, you should avoid separating the hierarchy and the data in two separate tables. Try this:


declare	@Locations table
    (ID int,
    ParentID int,
    Location varchar(50))

insert into @Locations (ID, ParentID, Location)
select 1, 0, 'Europe'
UNION
select 2, 1, 'Western Europe'
UNION
select 3, 2, 'Germany Europe'
UNION
select 4, 3, 'Bavaria'
UNION
select 5, 4, 'Berlin'

declare	@TargetLocation varchar(50)
set	@TargetLocation = 'Western Europe'

;
with LocationList as
    (select Locations.ID,
	    Locations.Location
    from    @Locations Locations
    where   Location = @TargetLocation
    UNION ALL
    select  Locations.ID,
	    Locations.Location
    from    @Locations Locations
	    inner join LocationList on Locations.ParentID = LocationList.ID)
select	ID,
	Location
from	LocationList

e4 d5 xd5 Nf6

mima
Starting Member

4 Posts

Posted - 2007-11-01 : 14:59:24

Thanks guys, much helped.

Reason why I have two separate tables is because location can have custom properties defined by user try cms.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-11-01 : 16:19:37

Sounds fishy to me...

e4 d5 xd5 Nf6

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 16:27:32

"Plus, mima, you should avoid separating the hierarchy and the data in two separate tables"

I don't imagine this is what you meant, but just for "intellectual exercise" we keep the data and hierarchy in tow separate tables:

We have Archive/Audit table on the Data to log any changes. The Data table records the "ID" of Parent and Sibling.

The Hierarchy table contains Path, Level, Sequence, etc. The Hierarchy table can be rebuilt (e.g. if a "node" moves to a different part of the table because a Parent Node has moved) without needing to trigger an audit record.

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-11-02 : 12:06:42

I don't store Path/Level/Sequence. That is dynamic, so I derive it from the parent/child relationship.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources