1 2 3 4 5 6 7 8 9 10 11 12 13
| SELECT C.id_candidat, CONCAT(UPPER(nom),' ',prenom) AS fullname, MAX(DATE_ADD(DATE_ADD(H.date,INTERVAL heure HOUR_SECOND),
INTERVAL duree MINUTE)) AS fin,
GROUP_CONCAT(DISTINCT(REPLACE(P.sur_audition, 'EV', 'choeur')) ORDER BY id_pupitre1) AS jurys,
GROUP_CONCAT(DISTINCT(H.id_horaire) ORDER BY H.id_horaire) AS dispos,
A.id_horaire, A.definitif
FROM preins_candidats C
JOIN preins_voeux V ON (C.id_candidat = V.id_candidat)
JOIN pupitres P
JOIN preins_dispo D ON (C.id_candidat = D.id_candidat)
JOIN preins_horaires H ON (D.id_horaire = H.id_horaire)
JOIN preins_affect A ON (C.id_candidat = A.id_candidat)
WHERE id_pupitre IN (id_pupitre1,id_pupitre2,id_pupitre3,id_pupitre4) AND (A.id_candidat IS NULL OR A.definitif = 'non' OR A.id_horaire NOT IN (SELECT DISTINCT(id_horaire) FROM preins_dispo D WHERE id_candidat = C.id_candidat))
GROUP BY C.id_candidat ORDER BY fin |
Partager