This discussion is archived
5 Replies Latest reply: Oct 9, 2012 4:07 AM by Pramukh RSS

Parent - child relationship in a table

Pramukh Newbie
Currently Being Moderated
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

I have the following table. The table contains a attribute called parentraid which specifies whether its a parent or a child. For the data given below, ra1,ra2,ra3 are parent and remaining are child with their respective parents specified in parentraid attribute.
CREATE TABLE  "ADDRESEARCHAREA" 
   (     "RAID" VARCHAR2(30) NOT NULL ENABLE, 
     "RANAME" VARCHAR2(30), 
     "RASTARTDATE" DATE, 
     "RAENDDATE" DATE, 
     "PARENTRAID" VARCHAR2(30), 
     "RASTATUS" VARCHAR2(30), 
      PRIMARY KEY ("RAID") ENABLE
   )

insert into addresearcharea values ('ra1','raname1',to_date('04/01/2012','mm/dd/yyyy'),'','','Active')
insert into addresearcharea values ('ra2','raname2',to_date('04/01/2012','mm/dd/yyyy'),'','','Active')
insert into addresearcharea values ('ra3','raname3',to_date('04/01/2012','mm/dd/yyyy'),'','','Active')
insert into addresearcharea values ('ra4','raname4',to_date('04/01/2012','mm/dd/yyyy'),'','ra1','Active')
insert into addresearcharea values ('ra5','raname5',to_date('04/01/2012','mm/dd/yyyy'),'','ra2','Active')
I am looking for the following output,
RAID RANAME PARENTRAID PARENTRANAME 
ra1 raname1 -  -  
ra2 raname2 -  -  
ra3 raname3 -  -  
ra4 raname4 ra1 raname1  
ra5 raname5 ra2 raname2  
Please help
  • 1. Re: Parent - child relationship in a table
    Mr Lonely Newbie
    Currently Being Moderated
    Try this
    select a.RAID as RAID, a.RANAME as RANAME, b.RAID as PARENTRAID,  b.RANAME as PARENTRANAME
    from ADDRESEARCHAREA a 
    left join ADDRESEARCHAREA b 
    on ( a.PARENTRAID=b.RAID)
    order by 1 
    And thank you for providing table ddl and sample insert statement

    Edited by: Anupam_Halder to add aliasing to the column
  • 2. Re: Parent - child relationship in a table
    jeneesh Guru
    Currently Being Moderated
    Basic hierarchial query..If you have only one level you can use just outer join also..
    SQL> select raid,raname,PARENTRAID,prior raname
      2  from addresearcharea
      3  start with PARENTRAID is null
      4  connect by prior raid = PARENTRAID
      5  order by 1;
    
    RAID       RANAME                         PARENTRAID                     PRIORRANAME
    ---------- ------------------------------ ------------------------------ ------------------------------
    ra1        raname1
    ra2        raname2
    ra3        raname3
    ra4        raname4                        ra1                            raname1
    ra5        raname5                        ra2                            raname2
    Edited by: jeneesh on Oct 9, 2012 11:51 AM
    Outer Join will be enough as displayed by Anupam_Halder.
    I did not notice that the RAID is a primary key...
  • 3. Re: Parent - child relationship in a table
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    Try this
    SQL> CREATE TABLE  "ADDRESEARCHAREA"
      2     (       "RAID" VARCHAR2(30) NOT NULL ENABLE,
      3     "RANAME" VARCHAR2(30),
      4     "RASTARTDATE" DATE,
      5     "RAENDDATE" DATE,
      6     "PARENTRAID" VARCHAR2(30),
      7     "RASTATUS" VARCHAR2(30),
      8      PRIMARY KEY ("RAID") ENABLE
      9     );
    
    Table created.
    
    SQL>
    SQL> insert into addresearcharea values ('ra1','raname1',to_date('04/01/2012','mm/dd/yyyy'),'','','Active');
    
    1 row created.
    
    SQL> insert into addresearcharea values ('ra2','raname2',to_date('04/01/2012','mm/dd/yyyy'),'','','Active');
    
    1 row created.
    
    SQL> insert into addresearcharea values ('ra3','raname3',to_date('04/01/2012','mm/dd/yyyy'),'','','Active');
    
    1 row created.
    
    SQL> insert into addresearcharea values ('ra4','raname4',to_date('04/01/2012','mm/dd/yyyy'),'','ra1','Active');
    
    1 row created.
    
    SQL> insert into addresearcharea values ('ra5','raname5',to_date('04/01/2012','mm/dd/yyyy'),'','ra2','Active');
    
    1 row created.
    
    SQL>
    SQL>
    SQL>
    SQL>
    SQL> select DISTINCT A.raid RAID,A.raname RANAME,A.parentraid PARENTRAID,
      2  case when A.parentraid=B.raid then A.raname else null end PARENT_NAME
      3  from ADDRESEARCHAREA A LEFT JOIN ADDRESEARCHAREA B ON (A.parentraid=B.raid) ORDER BY RAID ASC ;
    
    RAID RANAME  PARE PARENT_NAME
    ---- ------- ---- ------------------------------
    ra1  raname1
    ra2  raname2
    ra3  raname3
    ra4  raname4 ra1  raname4
    ra5  raname5 ra2  raname5
    
    SQL>
    SQL>
    Edited by: Chanchal Wankhade on Oct 8, 2012 11:41 PM

    Edited by: Chanchal Wankhade on Oct 8, 2012 11:41 PM
  • 4. Re: Parent - child relationship in a table
    jeneesh Guru
    Currently Being Moderated
    Chanchal Wankhade wrote:
    Hi,

    Try this
    SQL> select DISTINCT A.raid RAID,A.raname RANAME,A.parentraid PARENTRAID,
    2  case when A.parentraid=B.raid then A.raname else null end PARENT_NAME
    3  from ADDRESEARCHAREA A LEFT JOIN ADDRESEARCHAREA B ON (A.parentraid=B.raid) ORDER BY RAID ASC ;
    
    RAID RANAME  PARE PARENT_NAME
    ---- ------- ---- ------------------------------
    ra1  raname1
    ra2  raname2
    ra3  raname3
    ra4  raname4 ra1  raname4
    ra5  raname5 ra2  raname5
    
    SQL>
    SQL>
    Edited by: Chanchal Wankhade on Oct 8, 2012 11:41 PM

    Edited by: Chanchal Wankhade on Oct 8, 2012 11:41 PM
    Why there is a DISTINCT?
    Did you compare your output with OP's expected output?
  • 5. Re: Parent - child relationship in a table
    Pramukh Newbie
    Currently Being Moderated
    Thank you all for your quick reply. My question has been answered

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points