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!