[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Recursive Query : - Level skip possibility during recursive query searches for parent-child fields in a table

Hi,

I have requirement like this.
I have a db table with many columns and columns of my interest are 2 (Parent_id field and child_id field).
Given a parent_Id, I need find all the child Ids and then recursively find all the children of these child_Ids and so on.
I have succeded in getting this using "Start With" + "Connect By" and "PRIOR" clause in Oracle (9204 version of Oracle).

Select Distinct Child_IDS from the table start with parent_id = <input Parent_id>
Connect by parent_id = PRIOR child_id

This query runs absolutely fine.

My next requirement is I need to do this searching based on the LEVEL.
Suppose a LEVEL is given as input , then I need to do the searching  starting from that Level and all the child id's from that level ( then its children etc recursively) and ignore all the Ids before this given Level. For example, if the input recieved for LEVEL is 3 (also ZERO for skipping), the results should skip both level-1 and level-2 IDs as well as any children at level-3,and give all the children starting from level 4.

How can I get the Level ID with "Connect by" clause in the SQL query and how can I use it skip the level.

Help from all you good people required.

Thanks
Subbi
0
flashsubbu
Asked:
flashsubbu
1 Solution
 
Helena Markováprogrammer-analystCommented:
You can try this:

Select Child_IDS from table WHERE level>= <input level> start with parent_id = <input Parent_id>
Connect by parent_id = PRIOR child_id

Here is an example:
SQL> select level,o.os_objekt_id,o.os_objekt_id_nad from os_struktura o where level>=1
  2  start with o.os_objekt_id=10004 connect by o.os_objekt_id_nad=PRIOR o.os_objekt_id;

     LEVEL OS_OBJEKT_ID OS_OBJEKT_ID_NAD
---------- ------------ ----------------
         1        10004
         2        10005 10004
         3        10006 10005
         4        10009 10006
         4        10010 10006
         4        10011 10006
         4        10012 10006
         4        10013 10006
         4        10014 10006
         4        10017 10006
         4        10016 10006
         3        10007 10005
         4        10008 10007
         4        10018 10007

14 rows selected

SQL> select level,o.os_objekt_id,o.os_objekt_id_nad from os_struktura o where level>=2
  2  start with o.os_objekt_id=10004 connect by o.os_objekt_id_nad=PRIOR o.os_objekt_id;

     LEVEL OS_OBJEKT_ID OS_OBJEKT_ID_NAD
---------- ------------ ----------------
         2        10005 10004
         3        10006 10005
         4        10009 10006
         4        10010 10006
         4        10011 10006
         4        10012 10006
         4        10013 10006
         4        10014 10006
         4        10017 10006
         4        10016 10006
         3        10007 10005
         4        10008 10007
         4        10018 10007

13 rows selected

SQL> select o.os_objekt_id,o.os_objekt_id_nad from os_struktura o where level>=2
  2  start with o.os_objekt_id=10004 connect by o.os_objekt_id_nad=PRIOR o.os_objekt_id;

OS_OBJEKT_ID OS_OBJEKT_ID_NAD
------------ ----------------
       10005 10004
       10006 10005
       10009 10006
       10010 10006
       10011 10006
       10012 10006
       10013 10006
       10014 10006
       10017 10006
       10016 10006
       10007 10005
       10008 10007
       10018 10007

13 rows selected
0
 
flashsubbuAuthor Commented:
Thanks Henka.
Thanks for the Solution. It really worked.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now