How do I query Self Referencing Tables
-
I have a self referencing table called Group:
CREATE TABLE [dbo].[Groups] (
[GroupID] [int] IDENTITY (1, 1) NOT NULL ,
[GroupTypeID] [int] NOT NULL ,
[ParentID] [int] NULL ,
[GroupName] [varchar] (100) NOT NULL ,
)
Though I can pretty easily query as many levels as I want by specifying the levels in the query:
SELECT g1.groupname , g2.groupname
FROM GROUPS G1
LEFT OUTER JOIN GROUPS G2 on G1.GroupID = G2.ParentID
What I really want is an automatic way to get as many hierarchical levels as there are. Anybody have an idea how to do this without iteration (without a cursor).
Thanks in advance for your kind assistance.
-
The easiest way to do it is with a Stored Procedure.
If you are using Microsoft SQL Server check out this link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_5yk3.asp
-
In 7 / 2000 you'll need a temporary table and a loop to get the full heirarchy. Infinite loops are great this way too.
I believe 2005 can create a view with self referencing tables and it will populate the full heirarchy.
Julian Kuiters
juliankuiters.id.au -
Thomas:
Navigation of an adjacency list requires looping or recursion. Itzik Ben-Gan has some articles showing some techniques for doing this; check the SQL Server Magazine archives here: http://www.winnetmag.com/Authors/AuthorID/638/638.html
The other option is to use a different hierarchical model, e.g. the Nested Sets Model. Here is an introduction to that topic: http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=78287
You can find other info on both of these topics via a Google search. You may also want to consider Vadim Tropashko's Nested Intervals Model, which is much more complex but doesn't have the insertion penalty of the Nested Sets Model.
Julian:
SQL Server 2005 implements the WITH operator, which is called a Common-Table Expression. It is possible for these expressions to reference themselves, thereby becoming recursive. This does make navigation of adjacency lists much simpler to program, but under the covers it's still doing the same things. I'm looking forward to running performance tests on recursive CTEs vs. temp tables and loops to see whether MS was able to optimize the problem away. For now, we can only hope!
--
Adam Machanic
whoisactive -
Indeed. Form the what MS is saying at this point, it's too early still to be benchmarking they're still going through optimisations, but at least the functionality is there to begin with. But definately it takes some time after you declare the view to tie the heirarchy data together but I'm sure they understand how much this feature had been longed for.
Julian Kuiters
juliankuiters.id.au
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply