Problem Selecting from Hierarchy and Link Table

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)

Problem Selecting from Hierarchy and Link Table

Author

Topic

james_b
Starting Member

9 Posts

Posted - 2004-08-04 : 12:42:27

Hi - I'm having a problem writing the sql to do a join between a hierarchical table and a link table, for example:

Zone Table
***********

Zone_ID Zone_Name

1 | UK
2 | Yorkshire
3 | Buckinghamshire
4 | London
5 | Manchester
6 | USA
7 | Washington
8 | Florida
9 | Idaho

Zone_Hierarchy Table
********************

Zone_ID Subzone_ID

1 | 2
1 | 3
1 | 4
1 | 5
6 | 7
6 | 8
6 | 9

How would I go about writing a sql statement that retrieves the data like this:

Parent_Zone_Id Parent_Zone Subzone_Id Subzone

1 UK 2 Yorkshire
1 UK 3 Buckinghamshire
1 UK 4 London
1 UK 5 Manchester
1 USA 7 Washington
1 USA 8 Florida
1 USA 9 Idaho

Hope this makes it clear - thanks for any help :)

James

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-04 : 12:59:28

Select
Parent_Zone_Id = pZ.zone_Id,
Parent_Zone = pZ.zone_Name,
Subzone_Id = cZ.zone_Id,
Subzone = cZ.zone_Name
From zone_Hierarchy as zH
Inner Join zone as pZ
On zH.zone_Id = pZ.zone_Id
Inner Join zone as cZ
On zH.subzone_Id = cZ.zone_Id

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 16:20:48

You should normalize your data....

Brett

8-)

james_b
Starting Member

9 Posts

Posted - 2004-08-05 : 04:10:12

Seventhnight - works great - thanks :)

X002548 - why does it need normalizing? Surely it's just hierarchical?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources