select entity_tag from ecev;
ecev_tag
7
8
9
10
1
2
3
4
5
6


select digp_tag, cept_tag from dg
DIGP_TAG	CEPT_TAG
600		1
700		1
800		1
900		1
100		1
200		1
500		1
75		2


select * from entl  (Table that links TK and ecev and in turn enow)
TK_TAG	ENTITY_TAG
35	5
36	10
30	1
31	1
32	2
33	3
34	3

select * from dghi
PARENT_DIGP_TAG CHILD_DIGP_TAG
--------------- --------------
            600            100
            700            600
            900            100
            100             90
            100             80

select * from ENOW
ENTITY_TAG   DIGP_TAG   CEPT_TAG
---------- ---------- ----------
         7        600          1
         8        700          1
         9        800          1
        10        800          1
         1        100          1
         2        200          1
         3        500          1
         4        100          1
         5        100          1
         6         80          1
         1         75          2


SQL> select tk_tag,digp_tag, cept_tag from tk;

    TK_TAG   DIGP_TAG   CEPT_TAG
---------- ---------- ----------
        35         90          1
        36        900          1
        37        100          1
        30        100          1
        31        100          1
        32        200          1
        33        100          1
        34        100          1
        39        900          2


SQL> select * from dghi;

PARENT_DIGP_TAG CHILD_DIGP_TAG
--------------- --------------
            600            100
            700            600
            900            100
            100             90
            100             80


select
           d.ecev_tag entity_tag,
           a.digp_tag entity_owner ,
           c.digp_tag task_owner,
           c.tk_tag task_tag,
	   a.cept_tag enow_cept_tag,
	   c.cept_tag tk_cept_tag
from       enow a,
           entl b,
           tk c,
           ecev d ,
                (
                SELECT  distinct parent_digp_tag
                FROM dghi
                START WITH child_digp_tag=100
                CONNECT BY PRIOR parent_digp_tag= child_digp_Tag) e
where ( a.DIGP_TAG=100 or c.digp_tag=100 or a.digp_tag=e.parent_digp_tag or c.digp_tag=e.parent_digp_tag)
and    a.entity_tag=b.entity_tag(+)
and    b.tk_tag=c.tk_tag(+)
and    a.entity_tag=d.ecev_tag

Ideally i want the query to display

Entity_tag    entity_owner    task_owner    task_tag   enow_cept_tag  tk_cept_tag
1             100              90             30         1              1
1             100             100             31         1              1
3             100             100             33         1              1
4             100             NULL            NULL       NULL           NULL
5             100              90             35          1             1
NULL          NULL             100            37         NULL           1
7             600             NULL            NULL       1              NULL              
8             700             NULL            NULL       1              NULL
NULL          NULL            900             39         NULL           2              


We should get record 7  and 8 because, 100 is the child of digp_tags 600 and 700

We should get record 9 because, 900 is the parent of 100.


Does that make sense?