Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
raminaa niilian
Ranch Hand
Posts: 551
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



Regards, Jan

[ January 12, 2007: Message edited by: Jan Cumps ]
[ January 14, 2007: Message edited by: Jan Cumps ]
 
Kiran Joshi
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic