SELECT * FROM ((SELECT COUNT(id_offre) AS nb, id, nom, position
FROM ((SELECT o.id_offre, co.id_type_contrat AS id, cc.nom AS nom, cc.position
FROM offre o, agence a, config_type_contrat cc, offre_type_contrat co
WHERE a.id_agence=o.id_agence
AND o.actif=1 AND o.id_offre=co.id_offre AND co.id_type_contrat=cc.id_type_contrat AND ((LOWER(o.titre) LIKE '%agent%' OR LOWER(o.mots_cles) LIKE '%agent%' OR LOWER(o.description) LIKE '%agent%') AND (LOWER(o.titre) LIKE '%d%' OR LOWER(o.mots_cles) LIKE '%d%' OR LOWER(o.description) LIKE '%d%')) )
UNION
(SELECT o.id_offre, co.id_type_contrat AS id, cc.nom AS nom, cc.position
FROM offre o, entreprise e, config_type_contrat cc, offre_type_contrat co
WHERE e.id_entreprise=o.id_entreprise AND o.id_flux=0
AND o.actif=1 AND o.id_offre=co.id_offre AND co.id_type_contrat=cc.id_type_contrat AND ((LOWER(o.titre) LIKE '%agent%' OR LOWER(o.mots_cles) LIKE '%agent%' OR LOWER(o.description) LIKE '%agent%') AND (LOWER(o.titre) LIKE '%d%' OR LOWER(o.mots_cles) LIKE '%d%' OR LOWER(o.description) LIKE '%d%')) )) t1
GROUP BY id)
UNION
(SELECT 0 AS nb, cc.id_type_contrat AS id, cc.nom, cc.position
FROM config_type_contrat cc)
ORDER BY nb DESC, position ASC) t2 GROUP BY id ORDER BY nb DESC, position ASC
Postulez
Haut de page