File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes how to extract parents and childs from a table with self reference? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to extract parents and childs from a table with self reference?" Watch "how to extract parents and childs from a table with self reference?" New topic
Author

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

raminaa niilian
Ranch Hand

Joined: Jul 14, 2005
Posts: 551
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

Joined: Dec 20, 2006
Posts: 2510
    
  10

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 ]

OCUP UML fundamental and ITIL foundation
youtube channel
Kiran Joshi
Ranch Hand

Joined: Sep 04, 2005
Posts: 54
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to extract parents and childs from a table with self reference?