Hierarchical query with one to many relationship

Question

I have three 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 finally d_algo_maj:

NUM_MISE_A_JOUR    ID_ALGO VERSION_ALGO
--------------- ---------- ------------
             11        301 A301V1      

I want to create a query giving a result like :

id_algo   | version_algo |  has_maj
300       | A300V1       |  0       
301       | A301V1       |  1
302       | A302V1       |  1 

Where the first two columns are from d_algorithme, and has_maj is 0 or 1 depending on whether there is an algorithm referenced directly or indirectly in d_algo_maj. An indirect reference is via one or more d_algo_ope records, which together form a hierarchy.

For the sample data shown:

• 300: there is no algorithm or d_algo_ope record with id_algo = 1 and there is no d_algo_maj record with id_algo = 300.
• 301: there is a d_algo_maj record with id_algo = 301 (enough to make has_maj column be set to 1).
• 302: there is no d_algo_maj record with id_algo = 302. But there is a d_algo_ope record with num_operation = 301 and with id_algo = 302 which means that 302 algorithm references 301 algorithm (which has a maj) and hence the has_maj column should be set to 1.

Here is DDL and DML and other details (simplified from what I 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 into D_ALGORITHME


Insert into D_ALGORITHME (ID_ALGO,VERSION_ALGO,LIBELLE_ALGO) 
    values ('300','A300V1','Algo_300_V1');
Insert into D_ALGORITHME (ID_ALGO,VERSION_ALGO,LIBELLE_ALGO) 
    values ('301','A301V1','Algo_301_V1');
Insert into D_ALGORITHME (ID_ALGO,VERSION_ALGO,LIBELLE_ALGO) 
    values ('302','A302V1','Algo_302_V1');



REM INSERTING into D_ALGO_OPE

Insert into D_ALGO_OPE 
  (NUM_OPERATION,ID_ALGO,VERSION_ALGO,TYP_OPERATION,NUM_ORDRE) 
values ('300','301','A301V1','3','1');
Insert into D_ALGO_OPE (NUM_OPERATION,ID_ALGO,VERSION_ALGO,TYP_OPERATION,NUM_ORDRE) 
    values ('1','300','A300V1','3','1');
Insert into D_ALGO_OPE (NUM_OPERATION,ID_ALGO,VERSION_ALGO,TYP_OPERATION,NUM_ORDRE) 
    values ('301','302','A302V1','3','1');



REM INSERTING into D_ALGO_MAJ

Insert into D_ALGO_MAJ (NUM_MISE_A_JOUR,ID_ALGO,VERSION_ALGO) 
    values ('11','301','A301V1');

Answer 1

If I understand what you're doing and the links between your tables, then I think you can get the result you want with recursive subquery factoring (assuming you're on 11gR2 or higher):

with r (id_algo, version_algo, has_maj, last_id_algo, last_version_algo) as (
  select da.id_algo, da.version_algo, decode(dm.id_algo, null, 0, 1),
    da.id_algo, da.version_algo
  from d_algorithme da
  left join d_algo_maj dm
  on dm.id_algo = da.id_algo
  and dm.version_algo = da.version_algo
  union all
  select dao.id_algo, dao.version_algo, decode(dm.id_algo, null, 0, 1),
    dao.id_algo, dao.version_algo
  from r
  join d_algo_ope dao
  on dao.id_algo = r.last_id_algo
  and 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  to 1 default 0
select id_algo, version_algo, max(has_maj) as has_maj
from r
group by id_algo, version_algo
order by id_algo, version_algo;

   ID_ALGO VERSION_ALGO    HAS_MAJ
---------- ------------ ----------
       300 A300V1                0
       301 A301V1                1
       302 A302V1                1

The r CTE has an anchor member which outer-joins the d_algorithme rows to d_algo_maj, and uses decode to generate a flag at that level, or either zero or one. That part run on its 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 then looks up any matching d_aldo_ope record and outer joins that to d_algo_maj in the same way, getting the same flag. That part on its own would 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 you had more levels than you've shown in the sample data.

Combining those by finding the aggregate max(has_maj) for each ID/version means that a matching major record at any level gives an overall flag value of 1, and you only get 0 if there are no matches at all - which only happens for ID 300 with this data.