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 49 50 51 52 53
|
SELECT DISTINCT
DEMANDEUR.ID_NUM_NATIONAL,
CIVILITE.LBL_LIBELLE,
DEMANDEUR.LB_NOM_USAGE,
ARMEE_DEM.LB_ARMEE,
GRADE_DEM.LB_GRADE,
CONFLIT_DEM.LB_CONFLIT,
CARTE_INVALIDE.ID_CARTE,
DEMANDEUR.NUM_CARTE_COMBATTANT,
TITRE_STATUT.LB_TS,
COUNT(distinct DEMANDEUR.ID_NUM_NATIONAL)
FROM
QUALITE INNER JOIN DEMANDEUR ON (DEMANDEUR.ID_QUALITE=QUALITE.ID_QUALITE)
INNER JOIN SERVICE SERVICE_DEM ON (DEMANDEUR.ID_SERVICE=SERVICE_DEM.ID_SERVICE)
INNER JOIN CIVILITE ON (CIVILITE.ID_CIVILITE=DEMANDEUR.ID_CIVILITE)
INNER JOIN ARMEE ARMEE_DEM ON (ARMEE_DEM.ID_ARMEE=DEMANDEUR.ID_ARMEE)
INNER JOIN GRADE GRADE_DEM ON (GRADE_DEM.ID_GRADE=DEMANDEUR.ID_GRADE)
INNER JOIN CONFLIT CONFLIT_DEM ON (CONFLIT_DEM.ID_CONFLIT=DEMANDEUR.ID_CONFLIT)
INNER JOIN CARTE_INVALIDE ON (DEMANDEUR.ID_NUM_NATIONAL=CARTE_INVALIDE.ID_NUM_NATIONAL)
INNER JOIN TITRE_STATUT ON (CARTE_INVALIDE.ID_TYPE_TS=TITRE_STATUT.ID_TYPE_TS)
WHERE
(
DEMANDEUR.ID_NUM_NATIONAL In
(
SELECT DISTINCT
DEMANDEUR.ID_NUM_NATIONAL
FROM
DEMANDEUR INNER JOIN INSTANCE ON (DEMANDEUR.ID_NUM_NATIONAL=INSTANCE.ID_NUM_NATIONAL)
WHERE
( INSTANCE.ID_INSTANCE != 0 )
GROUP BY
DEMANDEUR.ID_NUM_NATIONAL
HAVING
COUNT(distinct INSTANCE.ID_INSTANCE) = 1
)
AND
QUALITE.COD_QUALITE = 'CS'
AND
SERVICE_DEM.COD_SERVICE = 'D37'
)
GROUP BY
DEMANDEUR.ID_NUM_NATIONAL,
CIVILITE.LBL_LIBELLE,
DEMANDEUR.LB_NOM_USAGE,
ARMEE_DEM.LB_ARMEE,
GRADE_DEM.LB_GRADE,
CONFLIT_DEM.LB_CONFLIT,
CARTE_INVALIDE.ID_CARTE,
DEMANDEUR.NUM_CARTE_COMBATTANT,
TITRE_STATUT.LB_TS; |
Partager