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
|
SELECT
SQL_CALC_FOUND_ROWS DISTINCT (annonce.id_annonce),
titre_francais,
annonce.id_membre,
...... ,
annonce_avant,
CASE WHEN date_annonce > '2017-01-14' THEN 1 ELSE 0 END AS news,
question.nb_reponse
FROM annonce
JOIN annonce_expedition ON annonce_expedition.id_annonce = annonce.id_annonce
JOIN membre ON membre.id_membre = annonce.id_membre
JOIN annonce_titre ON annonce.id_titre = annonce_titre.id_titre
JOIN annonce_libelle ON annonce.id_libelle = annonce_libelle.id_libelle
LEFT JOIN annonce_option ON annonce.id_annonce = annonce_option.id_annonce
LEFT JOIN (
SELECT
SUM(CASE WHEN annonce_reponse.id_annonce_question IS NOT NULL THEN 0 ELSE 1 END) AS nb_reponse,
annonce_question.id_annonce
FROM annonce_question
LEFT JOIN annonce_reponse ON annonce_question.id_annonce_question = annonce_reponse.id_annonce_question
######## ) question
ON annonce.id_annonce = question.id_annonce
WHERE annonce.id_membre ='1'
AND annonce_cloture = 0
AND annonce_vendu = 0
AND annonce_archive = 0
ORDER BY annonce_cloture
LIMIT 0,10 |
Partager