How to remove a recursive relationship

Hi,
I am building a db that contains folders with all info about a folder.
For example :
folder_id PK
folder_name
folder_size etc.
In my case, each folder may have 0 or more childern. A child itself
is unlikely going to have any children in itself. This is a recursive
relationship I'd like to remove in my DB design.
How can I represent this relatonship?
Thank you, DinoPlease elaborate on the issue, if you could. I'm not sure I understand; a
child folder which is the only child of another folder is unlikely to have
any children of its own?
How are you modelling the hierarchy itself? It doesn't HAVE to be recursive
(although that is one option). There are also hierarchical modelling
techniques like enumerated path and nested sets (search Google and you'll
get plenty of info on each) that are not recursive.
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:p2sj50p0pcihhqo9i4f0g0kv1thl98see7@.
4ax.com...
> Hi,
> I am building a db that contains folders with all info about a folder.
> For example :
> folder_id PK
> folder_name
> folder_size etc.
> In my case, each folder may have 0 or more childern. A child itself
> is unlikely going to have any children in itself. This is a recursive
> relationship I'd like to remove in my DB design.
> How can I represent this relatonship?
> Thank you, Dino|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:335813
I am gonna do it just like:
folder_id PK
parent_folder_id FK
folder_name
this way if the folder has a parent I will know it. I think this
solves the problem.
Thanks anyways,
Dino
On Thu, 18 Mar 2004 14:10:38 -0500, "Adam Machanic"
<amachanic@.air-worldwide.nospamallowed.com> wrote:

>Please elaborate on the issue, if you could. I'm not sure I understand; a
>child folder which is the only child of another folder is unlikely to have
>any children of its own?
>How are you modelling the hierarchy itself? It doesn't HAVE to be recursiv
e
>(although that is one option). There are also hierarchical modelling
>techniques like enumerated path and nested sets (search Google and you'll
>get plenty of info on each) that are not recursive.
>
>"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
> news:p2sj50p0pcihhqo9i4f0g0kv1thl98see7@.
4ax.com...
>

how to query the using recursive?

Hello,

I have the following tables:

Article(articleID,CategoryID,ArticleTitle)Categories(categoryID,ParentID,CategoryTitle)

I am trying to retrieve themain category ID for a specific article ID.

For example lets say I have this data:

Article:

1, 10 , "some title"2,10,"some title"3,11,"some title"

Categories:

1, NULL , "some title"2, 1, "some title"10, 2, "some title"11, 10 , "some title"

In this example I want to know who is the main category of article 3.

The query should return the answer:1

Thats because:

The article ID 3 is inside category 11.Parent for category 11 is 10.Parent for category 10 is 2. Parent for category 2 is 1and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category.Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables)

Do you have any suggestions for such query?

Thanks all.

check this post for a non-recursive technique for storing and traversing hierarchical data in sql:http://forums.asp.net/p/1084407/1611195.aspx#1611195

the technique is more fully described here:http://www.developerfusion.co.uk/show/4633/2/