Querying a Self Referencing Table

[TDK2001]

Hi

I have the following table:-

childid parentid description
---------------------------------------------
1 1 Company 1
2 1 Office 1
3 2 Ground Floor
4 3 Section
5 4 Room 1
6 4 Room 2
7 4 Room 3
8 1 Office 2
9 1 Office 3

If I want the details of childid 6 I would like to return:-
Company 1 - Office 1 - Room 2
or
childid 9 would return
Company 1 - Office 3

How do I write a sql statement to do this?

Regards
Tim

 

[essa2000]

Dear ;

What is relation between them i.e. ChildID and ParentID. Actually , I could not understand how you can get

Company 1 - office 1 - Room 2 for ChildID 6.

can you explain it so that we can find out any solution.

I think you will have to use Views to get your result. Please, think in this direction. I have worked on it but unable to get your result b/c I could not understand the link b/w them .

Regards,
Muhammad Essa Mughal

 

[TDK2001]

ChildID 6 has a parentid of 4 (Room 2) which inturn has a parentid of 3 (section) which inturn has a parentid of 2 (ground floor) which has a parentid of 1 which is the parent of them all.

 

[tlbroadbent]

You can use a recursive procedure. Check the following links.

http://www.sqlteam.com/ItemPrint.asp?ItemID=1353

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=19663

You may want to consider Joe Celko's solution - nested sets. It doesn't rely on recursion but will table redesign.

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci801943,00.html

http://www.intelligententerprise.com/001020/celko.shtml

http://www.dbpd.com/vault/9811/kamfn.shtml

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.