SELECT * FROM ((SELECT COUNT(id_offre) AS nb, id, nom, position
FROM ((SELECT o.id_offre, do.id_departement AS id, concat(d.code,' - ',d.nom) AS nom, 0 AS position
FROM offre o, agence a, departement d, departement_offre do
WHERE a.id_agence=o.id_agence
AND o.id_offre=do.id_offre AND do.id_departement=d.id_departement AND o.actif=1 )
UNION
(SELECT o.id_offre, do.id_departement AS id, concat(d.code,' - ',d.nom) AS nom, 0 AS position
FROM offre o, entreprise e, departement d, departement_offre do
WHERE e.id_entreprise=o.id_entreprise AND o.id_flux=0
AND o.id_offre=do.id_offre AND do.id_departement=d.id_departement AND o.actif=1 )
UNION
(SELECT o.id_offre, do.id_departement AS id, concat(d.code,' - ',d.nom) AS nom, 0 AS position
FROM offre o, recruteur_xml r, departement d, departement_offre do
WHERE r.id_recruteur=o.id_entreprise AND o.id_flux>0
AND o.id_offre=do.id_offre AND do.id_departement=d.id_departement AND o.actif=1 )) t1
GROUP BY id)
UNION
(SELECT 0 AS nb, d.id_departement AS id, concat(d.code,' - ',d.nom), 0 AS position
FROM departement d)
ORDER BY nb DESC, nom ASC) t2 GROUP BY id ORDER BY nb DESC, nom ASC
Postulez
Haut de page