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
| with employe as (
select 1 as IdEmp, 36000 as IdPays , 3 as IdEquipe, 250 as Salaire from dual union all
select 2 , 36001 , 1 , 400 from dual union all
select 3 , 36000 , 5 , 600 from dual union all
select 4 , 36002 , 1 , 200 from dual union all
select 5 , 36000 , 6 , 100 from dual union all
select 6 , 36000 , 18 , 300 from dual
),
equipe as (
select 36000 as IdPays, 3 as IdEquipe, 'EquipeA' as NomEquipe from dual union all
select 36000 , 0 , 'EquipeABis' from dual union all
select 36001 , 1 , 'EquipeB' from dual union all
select 36002 , 1 , 'EquipeC' from dual
)
select e.*,
coalesce(ep.NomEquipe,(select NomEquipe
from equipe ep2
where ep2.IdPays = e.IdPays
and ep2.IdEquipe = 0
)
) as NomEquipe
from employe e
left join equipe ep
on e.IdPays = ep.IdPays
and e.IdEquipe = ep.IdEquipe
order by IdEmp
IDEMP IDPAYS IDEQUIPE SALAIRE NOMEQUIPE
---------- ---------- ---------- ---------- ----------
1 36000 3 250 EquipeA
2 36001 1 400 EquipeB
3 36000 5 600 EquipeABis
4 36002 1 200 EquipeC
5 36000 6 100 EquipeABis
6 36000 18 300 EquipeABis |
Partager