5 Replies Latest reply: Oct 9, 2012 6:07 AM by Pramukh RSS

    Parent - child relationship in a table

    Pramukh
      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
          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
            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
              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
                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
                  Thank you all for your quick reply. My question has been answered