-------- note_infrastructure

SELECT  sc.id_societe,MIN(scd.niveau) as note_etat_equipement, cdf.fonction
FROM composant c
INNER JOIN  partie p ON c.id_partie=p.id_partie
INNER JOIN societe_composant sc ON c.id_composant=sc.id_composant
INNER JOIN societe_composant_desordre scd ON scd.id_societe_composant = sc.id_societe_composant
INNER JOIN composant_desordre_fonction cdf ON cdf.id_composant=sc.id_composant
WHERE p.partie LIKE '%genie civil%'
AND p.id_famille=1 OR p.id_famille=20
AND
  (composant LIKE '%Musoir%' OR composant LIKE '%bajoyer%' OR composant LIKE '%radier%' OR composant LIKE '%fond amont%'
   OR composant LIKE '%fond aval%' OR composant LIKE '%mur en retour%' OR composant LIKE '%estacade%'  )
AND
  ( cdf.fonction LIKE '%fonction_navigation%' OR  cdf.fonction  LIKE '%maintien_plan_eau%'
    OR cdf.fonction  LIKE '%protection_perennite%'  OR  cdf.fonction  LIKE '% %' )
GROUP BY sc.id_societe;