Home » SQL & PL/SQL » SQL & PL/SQL » Parent child relationship table
Parent child relationship table [message #188185] Thu, 17 August 2006 06:52 Go to next message
Shilpa_monika
Messages: 1
Registered: July 2006
Location: India
Junior Member
Hi,

I am having a table with Parent child relationship. Columns of this table are Parent_id, catgory_id, name. For parent record parent_id is NULL. Category_id = parent_id for parent child relation.

Now I want following result:

Parent Name1 --> child name1.1 --> grand child name1.1.1--> so on
Parent name1 --> child name1.2
Parent Name2
Parent name3 --> child name3.1
Parent name3 --> child name3.2
Parent name3 --> child name3.3

and so on.

How can I get this result if possible with single query. This is for Oracle 9i.


Regards,
Shilpa
Re: Parent child relationship table [message #188198 is a reply to message #188185] Thu, 17 August 2006 07:24 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
CONNECT BY and START WITH might be useful keywords to include them into a search engine.
Re: Parent child relationship table [message #188199 is a reply to message #188185] Thu, 17 August 2006 07:27 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
sys_connect_by_path too, indeed.
Re: Parent child relationship table [message #188201 is a reply to message #188199] Thu, 17 August 2006 07:29 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
If you have something like:

A is parent of B1, B2
B1 is parent of C1, C2, C3
C2 is parent of D1, D2
C3 is parent of D2

Try this:

select se.emp_name,
             SYS_CONNECT_BY_PATH(se.emp_name, ':')
        from Sample_Hierarchy sh, Sample_Emp se
       where sh.employee = Se.employee
         and sh.manager <> sh.employee
       start with  se.employee = 'A'
     connect by prior se.employee = sh.manager

[Updated on: Thu, 17 August 2006 07:30]

Report message to a moderator

Re: Parent child relationship table [message #188330 is a reply to message #188185] Fri, 18 August 2006 01:51 Go to previous message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
Hi,

Suppose as per your table having two
columns(parentname,childname,grandchildname).And childname, grandchildname have values 1

Now as per your requirement Parent Name1 --> child name1.1 --> grand child name1.1.1--> so on

The name should be incremented by .1 or what evere you want.

=20

"select (replace(childname,1,&v)), (replace(grandchildname,1,&v1)) from login_info where parentname=3D&u."

=20

this query will give you what ever value you want for the child ,grandchild and so on.

Hope this might solve your problem

THANKS & REGARDS

JAYDEEP MITRA

PH: 91-9440499806



Previous Topic: Help with Alias
Next Topic: reverse the cross tab in oracle
Goto Forum:
  


Current Time: Thu Dec 08 12:42:31 CST 2016

Total time taken to generate the page: 0.08791 seconds