SELECT c.*,d.libelle as libdept,d.preposition, ROUND(AVG(ca.note)) as note , count(ca.id_avis) as nb_note FROM club c  LEFT JOIN club_avis ca ON  ca.id_club = c.id INNER JOIN departements d ON d.id=c.id_dept WHERE c.id=54 GROUP BY c.id