1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
|
SELECT req_compt1.id_op, req_compt1.nom_op, req_compt1.nom_diffusion as diffusion, req_compt1.niveau_opl as niveau, req_compt1.count_opl, req_compt2.count_opl_total
FROM
(SELECT operateurs.id_op, operateurs.nom_op, pts_diffusion.id_diffusion, pts_diffusion.nom_diffusion, opl.niveau_opl, Count(opl.id_opl) AS count_opl
FROM pts_diffusion INNER JOIN ((opl INNER JOIN (operateurs INNER JOIN operateur_opl ON operateurs.id_op = operateur_opl.id_op) ON opl.id_opl = operateur_opl.id_opl) INNER JOIN opl_diffusion ON opl.id_opl = opl_diffusion.id_opl) ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion
WHERE (((opl.id_statut_opl)=2) AND ((operateur_opl.opl_a_reviser)=False))
GROUP BY operateurs.id_op, operateurs.nom_op, pts_diffusion.id_diffusion, pts_diffusion.nom_diffusion, opl.niveau_opl) AS req_compt1 RIGHT OUTER JOIN)
(SELECT pts_diffusion2.id_diffusion, pts_diffusion2.nom_diffusion, opl2.niveau_opl, (
SELECT COUNT (pts_diffusion.nom_diffusion)
FROM (opl INNER JOIN (pts_diffusion INNER JOIN opl_diffusion ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion) ON opl.id_opl = opl_diffusion.id_opl)
WHERE ((pts_diffusion.id_diffusion=pts_diffusion2.id_diffusion) AND (opl.niveau_opl = opl2.niveau_opl) AND (opl.id_statut_opl=2))
GROUP BY pts_diffusion.nom_diffusion, opl.niveau_opl) AS count_opl_total
FROM pts_diffusion AS pts_diffusion2 INNER JOIN (opl AS opl2 INNER JOIN opl_diffusion AS opl_diffusion2 ON opl2.id_opl=opl_diffusion2.id_opl) ON pts_diffusion2.id_diffusion=opl_diffusion2.id_diffusion
WHERE (((opl2.id_statut_opl)=2))
GROUP BY pts_diffusion2.nom_diffusion, pts_diffusion2.id_diffusion, opl2.niveau_opl
) AS req_compt2 ON
(((req_compt1.id_diffusion)=[req_compt2].[id_diffusion]) AND ((req_compt1.niveau_opl)=[req_compt2].[niveau_opl]))
WHERE count_opl IS NOT NULL
GROUP BY req_compt1.id_op, req_compt1.nom_op, req_compt1.nom_diffusion, req_compt1.niveau_opl, req_compt1.count_opl, req_compt2.count_opl_total
ORDER BY req_compt1.nom_op) |
Partager