Hierarchy

Hi to All!

Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way:

[Node | ParentId | ParentType | ChildId | ChildType]

But there is this nagging little voice saying it can be better

Cheers!

Nele

? Can you share some additional information? What do the "types" represent? Most of the time when I see the need for typing, it means that a table for that type should be created, and through key relationships this forms a natural hierarchy. In your system can the type hierarchy change? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Eburon@.discussions.microsoft.com> wrote in message news:f4c76476-0e56-4c53-939b-ffb7750ab228@.discussions.microsoft.com... Hi to All! Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way: [Node | ParentId | ParentType | ChildId | ChildType] But there is this nagging little voice saying it can be better Cheers! Nele|||

[same author as Eburon]

It is indeed a natural hierarchy. I assume that the type hierarchy cannot change, but a type can be absent. More in detail there are three types, t1, t2, t3:

t1 is always the root [level 1],|||? I would model this as three tables. T2 and T3 would both have self-referencing keys (i.e., "parent" keys)... CREATE TABLE T1 ( T1Id INT NOT NULL UNIQUE, ... //other attributes, including natural PK ) GO CREATE TABLE T2 ( T1id INT NULL REFERENCES T1 (T1id), T2id INT NOT NULL UNIQUE, parentT2id INT NULL REFERENCES T2 (T2id), ...//other attributes, PK, etc CONSTRAINT CheckIDsNOTNULL CHECK (COALESCE(T1id, T2id) IS NOT NULL) ) GO CREATE TABLE T3 ( T2id INT NULL REFERENCES T3 (T13id), T3id INT NOT NULL UNIQUE, parentT3id INT NULL REFERENCES T2 (T3id), ...//other attributes, PK, etc CONSTRAINT CheckIDsNOTNULL CHECK (COALESCE(T2id, T3id) IS NOT NULL) ) GO -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Nele@.discussions.microsoft..com> wrote in message news:27029b28-63cc-419f-8340-5be221c78682@.discussions.microsoft.com... [same author as Eburon] It is indeed a natural hierarchy. I assume that the type hierarchy cannot change, but a type can be absent. More in detail there are three types, t1, t2, t3: t1 is always the root [level 1], t2 may be added to go lower down the hierarchy [level 1.1 -> level 1 ... 1], and t3 may be added as a child [level 1 ... 1.1]. Is that sufficient additional information?|||I had not thought of that solution. Thank you!

Hierarchichal query in SQL Server 2000

Hi All,

How to implement a hierarchical query in SQL Server 2000.

Example ; I have an Oracle Query as below

SELECT LEVEL,employee_id, manager_id, first_name, last_name
FROM employee
START WITH employee_id = 1
CONNECT BY prior employee_id = manager_id;

I need to get the equivalent of this query in SQL Server 2000 .

In SQL Server 2005 i can achieve this using COMMON TABLE EXPRESSION .

Is there any way to implement this in SQL Server 2000Is there any way to implement this in SQL Server 2000not easily, no

if there is some maximum number of levels to the hierarchy (e.g. never more than eleven levels deep, from top executive to lowliest peon), then you can write a query with that number of LEFT OUTER JOINs|||You can do this efficiently using a loop. Check out this link, and then I will answer any other questions you have:
http://sqlblindman.googlepages.com/returningchildrecords|||Hi Blindman,

I am unable to open the link u provided .|||http://sqlblindman.googlepages.com/returningchildrecords
Are you getting an error?|||no error, url works just fine|||Here is a little tutorial i wrote some time ago that gives you what you want.

http://vbforums.com/showthread.php?t=366078