SELECT sc.id_societe,MAX(scd.niveau) as note_etat_equipement, cdf.fonctionFROM composant cINNER JOIN partie p ON c.id_partie=p.id_partieINNER JOIN societe_composant sc ON c.id_composant=sc.id_composantINNER JOIN societe_composant_desordre scd ON scd.id_societe_composant = sc.id_societe_composantINNER JOIN composant_desordre_fonction cdf ON cdf.id_composant=sc.id_composantWHERE p.partie LIKE '%genie civil%'AND p.id_famille=1 OR p.id_famille=20AND (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;