8 Replies Latest reply: Dec 8, 2012 1:34 AM by 978567 RSS

    Fetching data from two tables heirarchically

    978567
      My requirement is to fetch data from two tables heirarchically, by hitting DB only once. Means I should get data such a way that for each parent record I should be able to get child records. I tried it using ref cursor within the main cursor. But any how it did not work out.

      Any help will be highly appreciable.
        • 1. Re: Fetching data from two tables heirarchically
          908002
          No need of plsql....


          use connec by in sql query...

          Or search for heriarchical retrieval of data from sql
          • 2. Re: Fetching data from two tables heirarchically
            978567
            I checked hierarchical queries also but it will not solve my problem.

            I want output such a way that it should loop for parent record and inside that it should loop for child records.
            • 3. Re: Fetching data from two tables heirarchically
              BluShadow
              then please post some example data and expected output so that we can help you.

              {message:id=9360002}
              • 4. Re: Fetching data from two tables heirarchically
                BluShadow
                975564 wrote:
                I checked hierarchical queries also but it will not solve my problem.

                I want output such a way that it should loop for parent record and inside that it should loop for child records.
                Hierarchical query seems to work ok for me...
                SQL> ed
                Wrote file afiedt.buf
                
                  1  with par as (select 1 as id, 'Stores' as dept from dual union all
                  2               select 2, 'Sales' from dual union all
                  3               select 3, 'HR' from dual)
                  4      ,chld as (select 1 as id, 3 as par_id, 'Fred' as empname from dual union all
                  5                select 2, 1, 'John' from dual union all
                  6                select 3, 2, 'Sam' from dual union all
                  7                select 4, 2, 'Jenny' from dual union all
                  8                select 5, 3, 'Paul' from dual union all
                  9                select 6, 2, 'Mike' from dual union all
                 10                select 7, 1, 'Sally' from dual union all
                 11                select 8, 3, 'Jennifer' from dual union all
                 12                select 9, 1, 'Todd' from dual
                 13               )
                 14  --
                 15  -- end of test data - simulating two tables
                 16  --
                 17  select decode(par_id,null,'Dept: ','  Emp: ' )||nm
                 18  from
                 19        (
                 20         select -id as id, dept as nm, null as par_id from par
                 21         union all
                 22         select id, empname, -par_id from chld
                 23        )
                 24  connect by par_id = prior id
                 25  start with par_id is null
                 26* order siblings by nm
                SQL> /
                
                DECODE(PAR_ID,N
                ---------------
                Dept: HR
                  Emp: Fred
                  Emp: Jennifer
                  Emp: Paul
                Dept: Sales
                  Emp: Jenny
                  Emp: Mike
                  Emp: Sam
                Dept: Stores
                  Emp: John
                  Emp: Sally
                  Emp: Todd
                
                12 rows selected.
                • 5. Re: Fetching data from two tables heirarchically
                  978567
                  thank you.. here is the data

                  CREATE TABLE parent_tab (
                  id NUMBER,
                  description VARCHAR2(50),
                  CONSTRAINT parent_tab_pk PRIMARY KEY (id)
                  );

                  CREATE TABLE child_tab (
                  id NUMBER,
                  parent_id NUMBER,
                  description VARCHAR2(50),
                  CONSTRAINT child_tab_pk PRIMARY KEY (id),
                  CONSTRAINT child_parent_fk
                  FOREIGN KEY (parent_id)
                  REFERENCES parent_tab(id)
                  );

                  BEGIN
                  -- Parent data.
                  INSERT INTO parent_tab (id, description) VALUES (1, 'Parent 1');
                  INSERT INTO parent_tab (id, description) VALUES (2, 'Parent 2');
                  INSERT INTO parent_tab (id, description) VALUES (3, 'Parent 3');

                  -- Child Data.

                  INSERT INTO child_tab (id, parent_id, description) VALUES (1, 1, 'Child 1');
                  INSERT INTO child_tab (id, parent_id, description) VALUES (2, 1, 'Child 2');
                  INSERT INTO child_tab (id, parent_id, description) VALUES (3, 2, 'Child 3');
                  INSERT INTO child_tab (id, parent_id, description) VALUES (4, 2, 'Child 4');
                  INSERT INTO child_tab (id, parent_id, description) VALUES (5, 2, 'Child 5');
                  INSERT INTO child_tab (id, parent_id, description) VALUES (6, 3, 'Child 6');
                  COMMIT;
                  END;
                  /


                  DECLARE
                  l_parent parent_tab.description%TYPE;
                  l_children SYS_REFCURSOR;
                  l_child child_tab.description%TYPE;
                  -- Define a cursor containing a cursor expression.
                  CURSOR c_parent_child IS
                  SELECT p.description,
                  CURSOR(
                  SELECT c.description
                  FROM child_tab c
                  WHERE c.parent_id = p.id
                  ) children
                  FROM parent_tab p;
                  BEGIN
                  OPEN c_parent_child;
                  LOOP
                  -- Fetch each row from the parent query in turn.

                  FETCH c_parent_child
                  INTO l_parent,
                  l_children;
                  EXIT WHEN c_parent_child%NOTFOUND;

                  DBMS_OUTPUT.put_line('Parent: ' || l_parent); --here I want complete data from parent table+
                  -- Loop through the nested query returning each row.
                  LOOP
                  FETCH l_children
                  INTO l_child;
                  EXIT WHEN l_children%NOTFOUND;

                  DBMS_OUTPUT.put_line('...Child: ' || l_child); --here I want complete data from child table+
                  END LOOP;
                  END LOOP;
                  CLOSE c_parent_child;
                  END;

                  this is giving me parent and child ids. But I want whole data from two tables. I tried using record type where I declared all the fields of parent table and I was trying to put refrence to child table. but oracle does not support this
                  • 6. Re: Fetching data from two tables heirarchically
                    BluShadow
                    Works for me...
                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  select decode(parent_id,null,'Parent: ('||to_char(-id)||') ','  Child: ('||to_char(id)||') ')||description
                      2  from
                      3        (
                      4         select -id as id, description, null as parent_id from parent_tab
                      5         union all
                      6         select id, description, -parent_id from child_tab
                      7        )
                      8  connect by parent_id = prior id
                      9  start with parent_id is null
                     10* order siblings by description
                    SQL> /
                    
                    DECODE(PARENT_ID,NULL,'PARENT:('||TO_CHAR(-ID)||')','CHILD:('||TO_CHAR(ID)||')')||DESCRIPTION
                    ------------------------------------------------------------------------------------------------------
                    Parent: (1) Parent 1
                      Child: (1) Child 1
                      Child: (2) Child 2
                    Parent: (2) Parent 2
                      Child: (3) Child 3
                      Child: (4) Child 4
                      Child: (5) Child 5
                    Parent: (3) Parent 3
                      Child: (6) Child 6
                    
                    9 rows selected.
                    • 7. Re: Fetching data from two tables heirarchically
                      chris227
                      declare
                      l_desc varchar2(200);
                      begin
                      for rec in (select p.description p_desc, c.description c_desc
                                  from parent_tab p, child_tab c
                                  where p.id = c.parent_id
                                  order by p.id,c.id)
                      loop
                        case l_desc
                        when rec.p_desc
                        then DBMS_OUTPUT.PUT_LINE(chr(9)||'Child: '||rec.c_desc);
                        else DBMS_OUTPUT.PUT_LINE('Parent: '||rec.p_desc);
                             DBMS_OUTPUT.PUT_LINE(chr(9)||'Child: '||rec.c_desc);
                        end case;
                        l_desc := rec.p_desc;
                      end loop;
                      end;
                      
                      Parent: Parent 1
                           Child: Child 1
                           Child: Child 2
                      Parent: Parent 2
                           Child: Child 3
                           Child: Child 4
                           Child: Child 5
                      Parent: Parent 3
                           Child: Child 6
                      • 8. Re: Fetching data from two tables heirarchically
                        978567
                        Thanks for the help..my problem is resolved with this