SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hierarchies in SQL


Hierarchies in SQL

Author
Message
Jeff Strom
Jeff Strom
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 14
This article caught my attention because I deal with a related problem. I oversee the field structure for a company. The difference is that the structure is time-based; and a single Area can roll up to multiple parents based on the org type in use. This is primarily so I have the ability to re-state sales based on the structure that was in place on any particular date. The second item is that I am on SQL2K, with a near-term upgrade to 2005 coming, so I could not use any of the new CTE functionality.


To do this, I had to use two table, and the "Stack" table requires a rebuild any time the hierarchy changes. (luckily not often)

My tables are as follows:
create table GeoParent (
GeoParentId int not null identity(1,1),
AreaId int not null references dbo.Geo(ID),
ParentId int not null references dbo.Geo(ID),
DateStart datetime not null,
DateEnd datetime not null,
OrgTypeId int not null references dbo.GeoOrg(ID)
)

create table GeoStack (
GeoStackId int not null identity(1,1),
AreaId int not null references dbo.Geo(ID),
Lft int not null,
Rgt int not null,
Depth int not null,
DateStart datetime not null,
DateEnd datetime not null,
OrgTypeId int not null
)



The "Stack" table is built by walking through changes to the Parent table and building the hierachy tree in a temp table, then writing it to the table after each iteration, which is currently performed in an external visual basic DLL.

Once in place, I use code similar to danielk1 to get the hierachies:
SELECT S2.AreaId 
FROM GeoStack S1, GeoStack S2
WHERE S2.Lft BETWEEN S1.Lft AND S1.Rgt
AND @date BETWEEN S1.DateStart AND S1.DateEnd
AND @date BETWEEN S2.DateStart AND S1.DateEnd
AND S1.AreaId = @tid
AND S1.OrgTypeID=@org
AND S2.OrgTypeID=@org


Any thoughts?

Thanks,
Strom
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30011 Visits: 9730
Jeff Strom: That's basically the same as what I did, you just have it in two tables where I had it in one, and you have a time-sensitivity that I didn't have. Same concept though. What you've got should be pretty good for what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
asimsubedi
asimsubedi
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 23
nice
kenderman3
kenderman3
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 11
Very interesting!

Surfing the net is my hobby and posting blogs is my expression. I write different blog topics to various blog sites.
Online Bingo Reviews
Best Online Casino Games
Best Bingo Sites
Evil Kraig F
Evil Kraig F
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10591 Visits: 7660
Hey Gus,

Late to the party, I know, but you referenced this in another thread and I had a curiousity. Could you discuss the 'lazy updater' component a bit more. I'm not necessarily sure I understand how the 'temp range' vs. the 'real range' helps with update issues, since you're still locking and unlocking rows/pages/table using either column.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
autoexcrement
autoexcrement
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 818
Fascinating article! I have a couple of naive questions about the following bit of G code:


Adjacency (NodeID, ParentID) as -- Adjacency Query
(select 0, ID, ParentID
from dbo.HierarchyHybrid
where ID = @NodeID_in
andexists
(select*
from dbo.HierarchyHybrid h2
where h2.TopParentID = HierarchyHybrid.TopParentID
and RangeStart is null)



1. Is it supposed to have the "select 0, " or is that just a typo? It looks like the surrounding code is expecting 2 values there, not 3. If not a typo, I don't understand the "0" part.

2. Why doesn't this work?

Adjacency (NodeID, ParentID) as -- Adjacency Query
(select 0, ID, ParentID
from dbo.HierarchyHybrid
where ID = @NodeID_in
and RangeStart is null)



Thanks, regardless of whether you have time to reply.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2611 Visits: 3232
A very good solution and something that we've implemented in one of our solutions, the only difference is that we also have a RelationshipType table hung off the HeirarchyRelationship table.

The code snippet below, is an example of how we set this up to cater for differnt Geographic needs with an organisation.


CREATE TABLE [Common].[Geography] (
[GeographyId] INT IDENTITY (1, 1) NOT NULL,
[GeographyCode] NVARCHAR (50) NOT NULL,
[GeographyName] NVARCHAR (150) NOT NULL,
[Level] NVARCHAR (50) NULL
);
GO

CREATE TABLE [Common].[GeographyRelationship] (
[GeographyRelationshipId] INT IDENTITY (1, 1) NOT NULL,
[GeographyId] INT NOT NULL,
[ParentGeographyId] INT NOT NULL,
[RelationshipTypeId] SMALLINT NOT NULL
);
GO

CREATE TABLE [Common].[RelationshipType] (
[RelationshipTypeId] SMALLINT IDENTITY (1, 1) NOT NULL,
[RelationshipTypeName] NVARCHAR (20) NOT NULL,
[RelationshipTypeDescription] NVARCHAR (50) NOT NULL
);
GO




This allows us to create many different variations from a single list of nodes and at a variety of grains, some down only as far as sub-regions (State/County), others as deep as City Street.

Using a CTE the SQL is very simular to the Article, with only the addition of a join to the RelationshipType table and filtered on the required Type of Hierarchy.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
BOR15K
BOR15K
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 451
Interesting article, but I have a question, which hopefully will save me a lot of hours:
In Oracle 10 I do it in one select statement, using START WITH and CONNECT BY.
Now, migrating one of my customers to SQL 2008, is there anything close to it in SQL Server?

Thanks in advance
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40103 Visits: 18565
Thanks for the article Gus. I'm glad I caught it this time around.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search