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
| SELECT ASS.nom_assure,
ASS.pre_assure,
CAN.profil_candidat,
ASS.no_assure,
CAN.experience_candidat,
ASS.genre,
EMP2.max_emploi,
EMP3.description_emploi
FROM ASSURE ASS LEFT OUTER JOIN CANDIDAT CAN
ON ASS.no_assure = CAN.no_assure_fk,
CANDIDAT CAN LEFT OUTER JOIN
(select MAX (EMP.fin_emploi) as max_emploi
from EMPLOI EMP
group by EMP.no_assure_fk) EMP2
ON CAN.no_assure_fk = EMP2.no_assure_fk,
EMP2 LEFT OUTER JOIN EMPLOI EMP3
ON EMP3.fin_emploi = EMP2.max_emploi
WHERE ASS.is_candidat = 1
GROUP BY ASS.nom_assure,
ASS.pre_assure,
CAN.profil_candidat,
ASS.no_assure,
CAN.experience_candidat,
ASS.genre,
EMP2.max_emploi,
EMP3.description_emploi |
Partager