----------------------------------------- note_etat_equipement -----------------------------------------------
---porte amont

SELECT sc.id_societe,scd.niveau as note
FROM societe_composant_desordre scd
INNER JOIN societe_composant sc ON scd.id_societe_composant = sc.id_societe_composant
INNER JOIN composant_desordre_fonction cdf ON cdf.id_composant=sc.id_composant
INNER JOIN composant c ON c.id_composant=cdf.id_composant
INNER JOIN partie p ON c.id_partie=p.id_partie
WHERE  cdf.fonction LIKE '%fonction_navigation%' 
AND p.id_famille =1 OR p.id_famille=12
AND p.partie LIKE '%porte%amont%'
GROUP BY sc.id_societe

-- porte aval

SELECT sc.id_societe,scd.niveau as note
FROM societe_composant_desordre scd
INNER JOIN societe_composant sc ON scd.id_societe_composant = sc.id_societe_composant
INNER JOIN composant_desordre_fonction cdf ON cdf.id_composant=sc.id_composant
INNER JOIN composant c ON c.id_composant=cdf.id_composant
INNER JOIN partie p ON c.id_partie=p.id_partie
WHERE  cdf.fonction LIKE '%fonction_navigation%' 
AND p.id_famille =1 OR p.id_famille=12
AND p.partie LIKE '%porte%aval%'
GROUP BY sc.id_societe

----Systeme de manoeuvre porte amont

SELECT sc.id_societe,scd.niveau as note
FROM societe_composant_desordre scd
INNER JOIN societe_composant sc ON scd.id_societe_composant = sc.id_societe_composant
INNER JOIN composant_desordre_fonction cdf ON cdf.id_composant=sc.id_composant
INNER JOIN composant c ON c.id_composant = cdf.id_composant
INNER JOIN partie p ON c.id_partie=p.id_partie
WHERE  cdf.fonction LIKE '%fonction_navigation%' 
AND c.composant LIKE '%Système de manoeuvre%'
AND p.id_famille =1 OR p.id_famille=12
AND p.partie LIKE '%porte%amont%'
GROUP BY sc.id_societe

----Systeme de manoeuvre porte aval

SELECT sc.id_societe,scd.niveau as note
FROM societe_composant_desordre scd
INNER JOIN societe_composant sc ON scd.id_societe_composant = sc.id_societe_composant
INNER JOIN composant_desordre_fonction cdf ON cdf.id_composant=sc.id_composant
INNER JOIN composant c ON c.id_composant = cdf.id_composant
INNER JOIN partie p ON c.id_partie=p.id_partie
WHERE  cdf.fonction LIKE '%fonction_navigation%' 
AND c.composant LIKE '%Système de manoeuvre%'
AND p.id_famille =1 OR p.id_famille=12
AND p.partie LIKE '%porte%aval%'
GROUP BY sc.id_societe

----poste de commande

SELECT sc.id_societe,scd.niveau as note
FROM societe_composant_desordre scd
INNER JOIN societe_composant sc ON scd.id_societe_composant = sc.id_societe_composant
INNER JOIN composant_desordre_fonction cdf ON cdf.id_composant=sc.id_composant
INNER JOIN composant c ON c.id_composant = cdf.id_composant
WHERE  cdf.fonction LIKE '%fonction_navigation%' 
AND c.composant LIKE '%poste de commande%'
GROUP BY sc.id_societe