requete hierarchique + join condition

Version imprimable

Bonjour à tous

J'ai un problème d'écriture d'une requête.

J'ai un ensemble d'échantillons localisés par une table comprenant une hiérarchie.

Code:

1
2
3
4
5
6
 
Table 1 = Samples(ID,ID_TBL2) 
Table 2 = Localizations(ID,ID_PARENT,DESCRIPTION)
 
ID_TBL2 correspond au lien entre Table 1 et Table 2 ( cléf étrangère)
ID_PARENT est le lien de hierarchie dans la table 2

J'aimerais avoir une requete qui m'affiche pour chaque échantillon, l'ensemble de ses localisations.

i.e

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
Table 1                          Table 2 
-------                          -------
ID  ID_TBL2                      ID ID_PARENT DESCRIPTION
1   5                            1  0          dec1             
2   1                            2  1          dec2 
                                 5  2          dec3 
                                 0  null       root
=> requete 
   -------
(Table 1)ID       (Table 2) ID      (Table 2) Description
1                    5               dec3
1                    2               dec2
1                    1               dec1
1                    0               root
2                    1               dec1
2                    0               root

J'ai essayé de faire une jointure entre les deux tables puis de créer un connect by

Code:

1
2
3
4
5
6
 
select a.ID, b.ID 
from (Table 1) a,
     (Table 2) b
where a.ID = b.ID
connect by b.ID = prior b.ID_PARENT

mais ca ne fonctionne pas du tout comme je voudrais ( probablement car la condition de jointure est d'abord effectuée ).

Auriez vous des idées ?

Merci d'avance

Ici j'ai d'abord parcouru la hierarchie puis effectué la jointure à postériori :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
WITH TABLE1 AS
(
select 1 as ID, 5 as ID_TBL2 from dual union all                            
select 2      , 1            from dual
),
     TABLE2 AS
(
select 1 as ID, 0 AS ID_P, 'dec1' AS txt  from dual union all
select 2      , 1        , 'dec2'         from dual union all
select 5      , 2        , 'dec3'         from dual union all
select 0      , NULL     , 'root'         from dual
),
     HIER AS
(
select
    connect_by_root id as id_init,
    id,
    txt
from
    TABLE2
start with
    ID in (select ID_TBL2 from TABLE1)
connect BY
    ID = prior ID_P
)
select
    T1.ID,
    HI.ID,
    HI.txt
from
    TABLE1 T1
    inner join HIER HI
      on HI.id_init = T1.ID_TBL2
order by
    T1.ID asc,
    HI.ID desc;
 
ID	ID_1	TXT
1	5	dec3
1	2	dec2
1	1	dec1
1	0	root
2	1	dec1
2	0	root

un tout petit peu différement :

Code:

1
2
3
4
5
6
7
select connect_by_root t1_id,id, txt 
from (
  select (select t1.id from t1 where t1.id_tbl2=t2.id) t1_id, t2.* 
  from t2 
) 
connect by id = prior id_p
start with t1_id is not null;

Citation:

Envoyé par laurentschneider

un tout petit peu différement :

Code:

1
2
3
4
5
6
7
select connect_by_root t1_id,id, txt 
from (
  select (select t1.id from t1 where t1.id_tbl2=t2.id) t1_id, t2.* 
  from t2 
) 
connect by id = prior id_p
start with t1_id is not null;

Sauf erreur, ta requête ne fonctionne que s'il n'y a qu'un seul enregistrement dans t1 qui référence t2.
J'étais plutôt parti sur le même principe, mais avec une jointure externe:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
with table_1 as (select 1 id, 5 id_tbl2 from dual union all
                 select 2 id, 1 id_tbl2 from dual),
     table_2 as (select 1 id, 0 id_parent, 'dec1' description from dual union all
                 select 2 id, 1 id_parent, 'dec2' description from dual union all
                 select 5 id, 2 id_parent, 'dec3' description from dual union all
                 select 0 id, null id_parent, 'root' description from dual)
select connect_by_root id_1, id_2, description
from (select table_1.id id_1, table_2.id id_2, table_2.id_parent id_parent_2, table_2.description
      from table_1,
           table_2
      where table_2.id = table_1.id_tbl2(+))
Connect by prior id_parent_2 = id_2
start with id_1 is not null;
 
CONNECT_BY_ROOTID_1       ID_2 DESC
------------------- ---------- ----
                  2          1 dec1
                  2          0 root
                  1          5 dec3
                  1          2 dec2
                  1          1 dec1
                  1          0 root

03/09/2009, 12h30
speedy_g

Merci beaucoup pour vos réponses,

Je vais les essayer toutes, et essayer de comprendre aussi .... :).
(Je n'en suis qu'à mes débuts en sql.)

Johan
03/09/2009, 13h52
laurentschneider

Citation:

Envoyé par xdescamp

Sauf erreur, ta requête ne fonctionne que s'il n'y a qu'un seul enregistrement dans t1 qui référence t2.

oui, bonne remarque :king:

@xdescamp et laurentschneider

La requête fonctionne pour autant qu'il n'y aie qu'au plus un élément de table 1 dans chaque élément de table 2,
i.e le jeu de valeurs ainsi modifié ne produit plus de résultats corrects.
Il est toujours possible de mettre un 'select distinct' en en-tête mais pour 2300 records celle-ci met 0.6 secondes pour être effectuée.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 WITH table_1 AS (SELECT 1 id, 5 id_tbl2 FROM dual union ALL SELECT 2 id, 1 id_tbl2 FROM dual union ALL SELECT 3 id, 1 id_tbl2 FROM dual), table_2 AS (SELECT 1 id, 0 id_parent, 'dec1' description FROM dual union ALL SELECT 2 id, 1 id_parent, 'dec2' description FROM dual union ALL SELECT 5 id, 2 id_parent, 'dec3' description FROM dual union ALL SELECT 0 id, NULL id_parent, 'root' description FROM dual) SELECT connect_by_root id_1, id_2, description FROM (SELECT table_1.id id_1, table_2.id id_2, table_2.id_parent id_parent_2, table_2.description FROM table_1, table_2 WHERE table_2.id = table_1.id_tbl2(+)) Connect BY prior id_parent_2 = id_2 start WITH id_1 IS NOT NULL;
La requête de Waldar fonctionne quand à elle assez bien.

Par pure curiosité, que faudrait-il faire pour que la requête fonctionne ?.

Merci en tout cas pour votre aide.

Johan

03/09/2009, 17h17
xdescamp

Effectivement, quand il y a plusieurs éléments dans table_1, la partie hiérarchique de la requête ne fonctionne plus.:aie:

Là comme ça je ne vois pas trop comment on pourrait la corriger.
L'option de Waldar consistant à faire la partie hiérarchique tout de suite me paraît une meilleure option.:king: