sql - Hierarchical query with one to many relationship -

i have 3 tables; d_algorithme:

   id_algo version_algo libelle_algo                                      ---------- ------------ --------------------------------------------------        300 a300v1       algo_300_v1                                               301 a301v1       algo_301_v1                                               302 a302v1       algo_302_v1                                        

d_algo_ope:

num_operation    id_algo version_algo typ_operation  num_ordre ------------- ---------- ------------ ------------- ----------           300        301 a301v1                   3          1             1        300 a300v1                   3          1           301        302 a302v1                   3          1 

and d_algo_maj:

num_mise_a_jour    id_algo version_algo --------------- ---------- ------------              11        301 a301v1       

i want create query giving result :

id_algo   | version_algo |  has_maj 300       | a300v1       |  0        301       | a301v1       |  1 302       | a302v1       |  1  

where first 2 columns d_algorithme, , has_maj 0 or 1 depending on whether there algorithm referenced directly or indirectly in d_algo_maj. indirect reference via 1 or more d_algo_ope records, form hierarchy.

for sample data shown:

• 300: there no algorithm or d_algo_ope record id_algo = 1 , there no d_algo_maj record id_algo = 300.
• 301: there d_algo_maj record id_algo = 301 (enough make has_maj column set 1).
• 302: there no d_algo_maj record id_algo = 302. there d_algo_ope record num_operation = 301 , id_algo = 302 means 302 algorithm references 301 algorithm (which has maj) , hence has_maj column should set 1.

here ddl , dml , other details (simplified have in reality):

-- ddl -----------------------------  -- d_algorithme create table d_algorithme  (   id_algo number(10, 0) not null  , version_algo varchar2(6 byte) not null  , libelle_algo varchar2(50 byte) not null  ) ;  alter table d_algorithme add constraint ix_d_algorithme primary key  (   id_algo  , version_algo  );  -- d_algo_ope create table d_algo_ope  (   num_operation number(10, 0) not null  , id_algo number(10, 0) not null  , version_algo varchar2(6 byte) not null  , typ_operation number(6, 0) not null  , num_ordre number(10, 0) not null  );   alter table d_algo_ope add constraint ix_d_algo_ope primary key  (   id_algo  , version_algo  , num_ordre  ) ;  -- d_algo_maj create table d_algo_maj  (   num_mise_a_jour number(10, 0) not null  , id_algo number(10, 0) not null  , version_algo varchar2(6 byte) not null  )  ;  alter table d_algo_maj add constraint ix_d_algo_maj primary key  (   id_algo  , version_algo  , num_mise_a_jour  ) ;   -- dml ----------------   rem inserting d_algorithme   insert d_algorithme (id_algo,version_algo,libelle_algo)      values ('300','a300v1','algo_300_v1'); insert d_algorithme (id_algo,version_algo,libelle_algo)      values ('301','a301v1','algo_301_v1'); insert d_algorithme (id_algo,version_algo,libelle_algo)      values ('302','a302v1','algo_302_v1');    rem inserting d_algo_ope  insert d_algo_ope    (num_operation,id_algo,version_algo,typ_operation,num_ordre)  values ('300','301','a301v1','3','1'); insert d_algo_ope (num_operation,id_algo,version_algo,typ_operation,num_ordre)      values ('1','300','a300v1','3','1'); insert d_algo_ope (num_operation,id_algo,version_algo,typ_operation,num_ordre)      values ('301','302','a302v1','3','1');    rem inserting d_algo_maj  insert d_algo_maj (num_mise_a_jour,id_algo,version_algo)      values ('11','301','a301v1'); 

if understand you're doing , links between tables, think can result want recursive subquery factoring (assuming you're on 11gr2 or higher):

with r (id_algo, version_algo, has_maj, last_id_algo, last_version_algo) (   select da.id_algo, da.version_algo, decode(dm.id_algo, null, 0, 1),     da.id_algo, da.version_algo   d_algorithme da   left join d_algo_maj dm   on dm.id_algo = da.id_algo   , dm.version_algo = da.version_algo   union   select dao.id_algo, dao.version_algo, decode(dm.id_algo, null, 0, 1),     dao.id_algo, dao.version_algo   r   join d_algo_ope dao   on dao.id_algo = r.last_id_algo   , dao.version_algo = r.last_version_algo   left join d_algo_maj dm   on dm.id_algo = dao.num_operation ) cycle id_algo, version_algo set is_cycle  1 default 0 select id_algo, version_algo, max(has_maj) has_maj r group id_algo, version_algo order id_algo, version_algo;     id_algo version_algo    has_maj ---------- ------------ ----------        300 a300v1                0        301 a301v1                1        302 a302v1                1 

the r cte has anchor member outer-joins d_algorithme rows d_algo_maj, , uses decode generate flag @ level, or either 0 or one. part run on own woud get:

   id_algo version_algo    has_maj last_id_algo last_version_algo ---------- ------------ ---------- ------------ -----------------        300 a300v1                0          300 a300v1                   301 a301v1                1          301 a301v1                   302 a302v1                0          302 a302v1            

the recursive member looks matching d_aldo_ope record , outer joins that d_algo_maj in same way, getting same flag. part on own get:

   id_algo version_algo    has_maj last_id_algo last_version_algo ---------- ------------ ---------- ------------ -----------------        300 a300v1                0          300 a300v1                   301 a301v1                0          301 a301v1                   302 a302v1                1          302 a302v1            

but recursively if had more levels you've shown in sample data.

combining finding aggregate max(has_maj) each id/version means matching major record @ level gives overall flag value of 1, , 0 if there no matches @ - happens id 300 data.