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 45 46 47 48
| SQL> with t_maitre as (
2 select 1 as id_maitre, 'toto' as c from dual union all
3 select 2 as id_maitre, 'tata' as c from dual
4 ),
5 t_esc1 as (
6 select 1 as id_esc1, 1 as id_maitre, 'truc1' as c1 from dual union all
7 select 2 as id_esc1, 1 as id_maitre, 'truc2' as c1 from dual union all
8 select 3 as id_esc1, 2 as id_maitre, 'truc3' as c1 from dual union all
9 select 4 as id_esc1, 2 as id_maitre, 'truc4' as c1 from dual
10 ),
11 t_esc2 as (
12 select 1 as id_esc2, 1 as id_maitre, 'chose1' as c2 from dual union all
13 select 2 as id_esc2, 1 as id_maitre, 'chose2' as c2 from dual union all
14 select 3 as id_esc2, 1 as id_maitre, 'chose3' as c2 from dual union all
15 select 4 as id_esc2, 1 as id_maitre, 'chose4' as c2 from dual union all
16 select 5 as id_esc2, 2 as id_maitre, 'chose5' as c2 from dual union all
17 select 6 as id_esc2, 2 as id_maitre, 'chose6' as c2 from dual union all
18 select 7 as id_esc2, 2 as id_maitre, 'chose7' as c2 from dual union all
19 select 8 as id_esc2, 2 as id_maitre, 'chose8' as c2 from dual
20 )
21 select *
22 from t_maitre t
23 join t_esc1 e1 on e1.id_maitre = t.id_maitre
24 join t_esc2 e2 on e2.id_maitre = t.id_maitre
25 /
ID_MAITRE C ID_ESC1 ID_MAITRE C1 ID_ESC2 ID_MAITRE C2
---------- ---- ---------- ---------- ----- ---------- ---------- ------
1 toto 2 1 truc2 1 1 chose1
1 toto 1 1 truc1 1 1 chose1
1 toto 2 1 truc2 2 1 chose2
1 toto 1 1 truc1 2 1 chose2
1 toto 2 1 truc2 3 1 chose3
1 toto 1 1 truc1 3 1 chose3
1 toto 2 1 truc2 4 1 chose4
1 toto 1 1 truc1 4 1 chose4
2 tata 4 2 truc4 5 2 chose5
2 tata 3 2 truc3 5 2 chose5
2 tata 4 2 truc4 6 2 chose6
2 tata 3 2 truc3 6 2 chose6
2 tata 4 2 truc4 7 2 chose7
2 tata 3 2 truc3 7 2 chose7
2 tata 4 2 truc4 8 2 chose8
2 tata 3 2 truc3 8 2 chose8
16 rows selected.
SQL> |
Partager