how to extract parents and childs from a table with self reference?

[raminaa niilian]

Hi
Thank you for reading my post.
I have a Category table which contains all categories and there is field for each record to show the parent of that categories.

some categories has no parent so they are roo categories which has -1 as thier parentCategoryID.

now i should extract each category and its childs.

here is my way:

-select all records which has parentCategoryID=-1
-in a loop which its length is equal to above resultset execute a query and select all records with parentCategoryID equal to current categoryID.


Is it a good way, is there some better way to extract categories and sub categories?

Thanks

[Jan Cumps]

Originally posted by raminaa niilian:

I have a Category table which contains all categories and there is field for each record to show the parent of that categories.

some categories has no parent so they are roo categories which has -1 as thier parentCategoryID.

now i should extract each category and its childs.

here is my way:

-select all records which has parentCategoryID=-1
-in a loop which its length is equal to above resultset execute a query and select all records with parentCategoryID equal to current categoryID.


Is it a good way, is there some better way to extract categories and sub categories?

Thanks

This query will return your results if your database supports the union clause:

select 
  c1.category as name
, c2.category as parent 
from 
  category c1
, category c2
where 
  c1.parentCategoryID = c2.categoryid
union
select 
  category as name
, null as parent 
from 
  category 
where 
  parentcategoryid = -1

Regards, Jan

[ January 12, 2007: Message edited by: Jan Cumps ]
[ January 14, 2007: Message edited by: Jan Cumps ]

[Kiran Joshi]

Perhaps this is what you want more precisely

select pid parentid,pname parentname,id childid,name childname
from
category child,
(
select id pid,name pname
from category
where
parentid=-1
) parent
where child.parentid=parent.pid