CREATE VIEW `view_affectation_produits` AS
select `afp`.`id`, `afp`.`promotion_code`, `pm`.`libelle` as `promotion`, 
        `afp`.`gamme_code`, `g`.`libelle` as `gamme`, `afp`.`famille_code`, `f`.`libelle` as `famille`, 
        `afp`.`marque_code`, `m`.`libelle` as `marque`, `afp`.`sousfamille_code`, `sf`.`libelle` as `sousfamille`, 
        `afp`.`produit_code`, `pr`.`libelle` as `produit`, `afp`.`groupe_code`, `gp`.`libelle` as `groupe`
from `affectation_produits` as `afp`
    left join `promotions` as `pm` on `afp`.`promotion_code` = `pm`.`code`
    left join `gammes` as `g` on `afp`.`gamme_code` = `g`.`code`
    left join `familles` as `f` on `afp`.`famille_code` = `f`.`code`
    left join `marques` as `m` on `afp`.`marque_code` = `m`.`code`
    left join `sousfamilles` as `sf` on `afp`.`sousfamille_code` = `sf`.`code`
    left join `groupes` as `gp` on `afp`.`groupe_code` = `gp`.`code`
    left join `produits` as `pr` on `afp`.`produit_code` = `pr`.`code`;