Recursive SQL select query in Oracle 11

Question

I have 4 different tables, bommodule, bomitem and mapbomitemmodule, mapbomparentsubmodule.

bommodule-table:

CREATE TABLE "BOMMODULE"
(
  "MODULEID"         NUMBER(10,0) NOT NULL ENABLE,
  "ISROOTMODULE"     NUMBER(1,0) NOT NULL ENABLE,
  "MODULENAME"       VARCHAR2(255 CHAR),
  .....
)

bomitem-table:

CREATE TABLE "BOMITEM"
  (
    "ITEMID"     NUMBER(10,0) NOT NULL ENABLE,
    ....
  )

mapbomitemmodule-table: (This table maps the items to one or more modules).

CREATE TABLE "SSIS2"."MAPBOMITEMMODULE"
  (
    "ITEMID"   NUMBER(10,0) NOT NULL ENABLE,
    "MODULEID" NUMBER(10,0) NOT NULL ENABLE,
    CONSTRAINT "MAP_ITEM_MODULE_FK" FOREIGN KEY ("ITEMID") REFERENCES "BOMITEM" ("ITEMID") ENABLE,
    CONSTRAINT "MAP_MODULE_ITEM_FK" FOREIGN KEY ("MODULEID") REFERENCES "BOMMODULE" ("MODULEID") ENABLE
  )

mapbomparentsubmodule-table: (This table maps the module to submodules)

CREATE TABLE "MAPBOMPARENTSUBMODULE"
  (
    "PARENTMODULEID" NUMBER(10,0) NOT NULL ENABLE,
    "SUBMODULEID"    NUMBER(10,0) NOT NULL ENABLE,
    CONSTRAINT "PARENTMODULE_SUBMODULE_FK" FOREIGN KEY ("SUBMODULEID") REFERENCES "BOMMODULE" ("MODULEID") ENABLE,
    CONSTRAINT "SUBMODULE_PARENTMODULE_FK" FOREIGN KEY ("PARENTMODULEID") REFERENCES "BOMMODULE" ("MODULEID") ENABLE
  )

So imagine a structure something like this.

root module
  submodule 1
    submodule 2
      submodule 3
        submodule 4
          submodule 5
          item 5
          item 6
          item 7
      item 2
      item 3
      item 4
  item 1

I need to find out all items that belong to a specific moduleId. All items on all submodule levels should be listed.

How can I do this? I am using Oracle 11 as database.

Thanks a lot for your help, much appreciate it!

Could you post the sample data for your example as well?

Chetter Hummin
– Chetter Hummin

2012-04-26 07:51:45 +00:00
Commented Apr 26, 2012 at 7:51 — Chetter Hummin
– Chetter Hummin, Commented Apr 26, 2012 at 7:51

turbanoff · Accepted Answer · 2012-04-26 10:59:48Z

Thanks for the interesting question

1 step. Prepare table for canonical oracle hierarhy.

select PARENTMODULEID, SUBMODULEID, 1 HTYPE
from MAPBOMPARENTSUBMODULE

union all

select null, MODULEID, 1 --link root to null parent
from BOMMODULE B
where ISROOTMODULE = 1

union all 

select MODULEID, ITEMID, 2
from MAPBOMITEMMODULE

2 step. Expand the hierarchy by using connect by

select PARENTMODULEID
      ,SUBMODULEID
      ,HTYPE
      ,level
      ,lpad('|', level*3, '|')
from
(
    select PARENTMODULEID, SUBMODULEID, 1 HTYPE
    from MAPBOMPARENTSUBMODULE

    union all

    select null, MODULEID, 1
    from BOMMODULE B
    where ISROOTMODULE = 1

    union all 

    select MODULEID, ITEMID, 2
    from MAPBOMITEMMODULE
) ALL_HIER
connect by prior SUBMODULEID = PARENTMODULEID
       and prior HTYPE = 1 --parent is always module
start with ALL_HIER.PARENTMODULEID is null
order siblings by HTYPE

3 step. Last. Join with value tables.

select PARENTMODULEID
      ,SUBMODULEID
      ,HTYPE
      ,ALL_VAL.VAL
      ,level
      ,rpad('|', level * 3, ' ') || ALL_VAL.VAL
from
(
    select PARENTMODULEID, SUBMODULEID, 1 HTYPE
    from MAPBOMPARENTSUBMODULE

    union all

    select null, MODULEID, 1
    from BOMMODULE B
    where ISROOTMODULE = 1

    union all 

    select MODULEID, ITEMID, 2
    from MAPBOMITEMMODULE
) ALL_HIER
,(
    select MODULEID VAL_ID, MODULENAME VAL, 1 VTYPE
    from BOMMODULE

    union all

    select ITEMID, 'item '||ITEMID, 2
    from BOMITEM
) ALL_VAL
where ALL_VAL.VAL_ID = ALL_HIER.SUBMODULEID
  and ALL_VAL.VTYPE = ALL_HIER.HTYPE
connect by prior SUBMODULEID = PARENTMODULEID
       and prior HTYPE = 1
start with ALL_HIER.PARENTMODULEID is null
order siblings by HTYPE

3 revs, 2 users 88% · Accepted Answer · 2012-04-26 11:38:21Z

1

Start by investigating the use of a hierarchical query, using the CONNECT BY clause. It is designed for exactly this type of model.

The principle for multiple tables is the same as that for a single table:

i. You identify "starting rows".
ii. You define the logic by which you identify the next level of rows based on the "current" set.

In some cases it can help to define the hierarchical query in an inline view, particularly if a single table holds all the data required for identifying both the starting rows and the connection between levels.

These queries are always tricky, and as with many SQL issues it often helps to start off simple and build up the complexity.

edited Apr 26, 2012 at 11:38

community wiki

3 revs, 2 users 88%
David Aldridge

3 Comments

nimrod Over a year ago

These examples are based on one table. But I have 4. So it is not that helpful :(

Ollie Over a year ago

@dooonot, you could select the data from all four tables using a WITH clause and then use the hierarchical query on thae results to get the output you need. Therefore @DavidAldridge is correct.

nimrod Over a year ago

Can you please help with code if it is that logic for you two?

Collectives™ on Stack Overflow

Recursive SQL select query in Oracle 11

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related