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

SQL Server Development (2000)

Hierarchy Trees

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-30 : 07:24:45

Malcolm writes "Hi,

I am working on a solution to enable users to select the various elements of a hierarchy from different levels and return the data as a report.

For example the indented list would appear as follows:

Deposits,placmements,loans & securities (Level 1)
--Deposits,placements & loans (Level 2)
----Unlisted corporations (Level 3)
--Securities (Level 2)
----Securitised instruments (Level 3)
Unlisted (Level 1)
--Corporations & private unit trusts (Level 2)
--Retail/Public offer trusts (Level 2)
----Property (Level 3)
----Equities (Level 3)
----Cash management (Level 3)
----Other (Level 3)

If you create seperate tables for each level and then link these to form the above hierarchy then you are able to return the full result set.

The problem I have is thats its possible that a user may select for example 'Property (Level 3)' and 'Securities (Level 2)'. Because they are from different levels and more importantly branches then then the query returns a empty result set.
The table struture would be some like the following:-

Level_1_tbl

Level_1_Key Level_1_Description
-----------------------------------------------------
1 Deposits,placmements,loans & securities
2 Unlisted

Level_2_tbl

Level_1_Key Level_2_Key Level_2_Description
-----------------------------------------------------
1 20 Deposits,placements & loans
1 30 Securities
2 40 Corps & private unit trusts
2 50 Ret/Public offer trusts

Level_3_tbl

Level_2_Key Level_3_Key Level_3_Description
-----------------------------------------------------
20 200 Unlisted corporations
30 300 Securitised instruments
40 400 Property
40 500 Equities
40 600 Cash management
40 700 Other

SQL used would be the following:

SELECT
a1.Level_1_Description,
a2.Level_2_Description,
a3.Level_3_Description
FROM
Level_1_tbl a1
inner join Level_2_tbl a2
on a1.Level_1_Key=a2.Level_1_Key
inner join Level_3_tbl a3
on a2.Level_1_Key=a3.Level_1_Key

Any help would be greatly appreciated I've been looking for a solution for a while. I'd imagine that the table data need to be modelled in another way?

Regards,

Malcolm"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-30 : 07:26:22

Refer these
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp

Madhivanan

Failing to plan is Planning to fail

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-09-30 : 12:45:20

You can also look at this link:

http://www.sql-server-helper.com/functions/get-tree-path.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources