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
|
SELECT s1.id, s1.titre, s1.joueur, s1.niveau, s1.description, s1.id_societe, s1.etat, s1.type,
s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat as etat_societe, AVG(a1.note_escape) as note_escape, AVG(a1.note_ambiance) as note_ambiance,
t2.path as img_path, t2.name as img_name, d1.id_scenario, d1.youtube, d1.duree, d1.multi_level, d1.tx_fouille, d1.tx_manipulation, d1.tx_reflexion, d1.tx_sortie, d1.handicap, d1.salle, d1.lg, d1.age_min, d1.remarque
FROM scenario AS s1
INNER JOIN societe AS s2 ON (s1.id_societe = s2.id)
LEFT JOIN avis AS a1 ON (s1.id=a1.id_scenario)
LEFT JOIN scenario_detail AS d1 ON (s1.id = d1.id_scenario)
LEFT JOIN (
SELECT i1.path, i1.name, f2.id_scenario ,
row_number() over(partition by id_scenario order by id asc) as RN
FROM file AS i1
INNER JOIN scenario_img AS f2 ON (i1.id_file=f2.id_file)
WHERE f2.id_scenario IN (
SELECT id
FROM scenario
WHERE scenario.id = f2.id_scenario
)
) t2
ON t2.id_scenario = s1.id
WHERE
coalesce(t2.RN, 1)=1
GROUP BY s1.id, s1.titre, s1.joueur, s1.niveau, s1.description, s1.id_societe, s1.etat, s1.type, s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat, t2.path, t2.name, d1.id_scenario, d1.youtube, d1.duree, d1.multi_level, d1.tx_fouille, d1.tx_manipulation, d1.tx_reflexion, d1.tx_sortie, d1.handicap, d1.salle, d1.lg, d1.age_min, d1.remarque |
Partager