Child/ Parent relationship within table

Hi everyone, I have a categories table which has the following main
attributes
CategoryName, CategoryID <- Identity , ParentCategoryID
What I need help doing is constructing a Procedure/ SQL query where I can
show the expanded relationships for each record in the table.
e.g. If I have three records in the table (Following the attributes
described above)
ParentCategory, 1, 0
ChildCategory, 2,1
SubChildCategory, 3,2
I want to be able to dynamically return the following information when I
execute the query (Each row looks at the parent id and concatenates itself
to its parents CategoryName).
ParentCategory
ParentCategory/ChildCategory
ParentCategory/ChildCategory/SubChildCategory
Thanks in advance
MarlkEverything you are doing is wrong. Let's get back to the basics of an
RDBMS. Rows are not records; fields are not columns; tables are not
files; there is no sequential access or ordering in an RDBMS, so
"first", "next" and "last" are totally meaningless.
Stop using IDENTITY and learn what a relational key is.
Get a copy of TREES & HIERARCHIES IN SQL You are trying to write
(uughh!) procedural code to build a traversal.that will create a path.
This is not the best way; google a "nested sets model" instead.|||There are many, many resources for describing ways to efficiently model
hierarchies and trees in SQL.
Google for the following topics: Nested Sets, Nested Intervals, Adjacency
List, Materialized Path
Here is a good starting point for finding information about this topic:
http://troels.arvin.dk/db/rdbms/links/#hierarchical
As a first step, you might consider creating a new table to manage the
relationship between categories - right now, you are modeling both the
category and the relationship between categories in the same table.
Normalizing the design can give you added flexibility depending on your
requirements.
What you have here is basically an Adjacency List model. This model has
excellent characteristics with regard to modifying the layout of the
hierarchy; you simply change the ParentCategoryID of a node to a different
value, and you instantly "move" that node and all referencing nodes to a
different location in the hierarchy. However, it does not work very well for
retrieving the structure, as you are seeing. SQL Server Books Online has a
section titled "Expanding Hierarchies" that describes an iterative process
of querying the hierarchy that involves using a temporary table as a stack,
but SQL is really optimized for set-based operations. You will probably find
something at the link above that better meets your needs.
"Mark" <dont@.spam.me> wrote in message
news:%233O9T%23KuFHA.1560@.TK2MSFTNGP09.phx.gbl...
> Hi everyone, I have a categories table which has the following main
> attributes
> CategoryName, CategoryID <- Identity , ParentCategoryID
> What I need help doing is constructing a Procedure/ SQL query where I can
> show the expanded relationships for each record in the table.
> e.g. If I have three records in the table (Following the attributes
> described above)
> ParentCategory, 1, 0
> ChildCategory, 2,1
> SubChildCategory, 3,2
> I want to be able to dynamically return the following information when I
> execute the query (Each row looks at the parent id and concatenates itself
> to its parents CategoryName).
> ParentCategory
> ParentCategory/ChildCategory
> ParentCategory/ChildCategory/SubChildCategory
> Thanks in advance
> Marlk
>
>
>
>|||Whoa! Cool, thanks for the links
Cheers
Mark
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:ekyuNZLuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> There are many, many resources for describing ways to efficiently model
> hierarchies and trees in SQL.
> Google for the following topics: Nested Sets, Nested Intervals, Adjacency
> List, Materialized Path
> Here is a good starting point for finding information about this topic:
> http://troels.arvin.dk/db/rdbms/links/#hierarchical
> As a first step, you might consider creating a new table to manage the
> relationship between categories - right now, you are modeling both the
> category and the relationship between categories in the same table.
> Normalizing the design can give you added flexibility depending on your
> requirements.
> What you have here is basically an Adjacency List model. This model has
> excellent characteristics with regard to modifying the layout of the
> hierarchy; you simply change the ParentCategoryID of a node to a different
> value, and you instantly "move" that node and all referencing nodes to a
> different location in the hierarchy. However, it does not work very well
for
> retrieving the structure, as you are seeing. SQL Server Books Online has a
> section titled "Expanding Hierarchies" that describes an iterative process
> of querying the hierarchy that involves using a temporary table as a
stack,
> but SQL is really optimized for set-based operations. You will probably
find
> something at the link above that better meets your needs.
> "Mark" <dont@.spam.me> wrote in message
> news:%233O9T%23KuFHA.1560@.TK2MSFTNGP09.phx.gbl...
can
itself
>