Edit History

Autre solution, serait de pouvoir faire référence aux alias dans where clause ce qui n'est pas possible mais y a t'il moyen de contourner?

select o.id_order as numeroCommande, o.date_add as dateCommande, YEAR(o.date_add) as anneeCommande, MONTH(o.date_add) as moisCommande,
                        oi.date_add as dateFacture, oi.number as numeroFacture, YEAR(oi.date_add) as anneeFacture, MONTH(oi.date_add) as moisFacture,
                        gl.name as categorieClient/*,  as tiersFacturé*/,
                        c.lastname as nomContact, c.firstname as prenomContact,
                        a.postcode as cpFacturation, a.city as villeFacturation,
                        cl.name as paysFacturation,p.id_product, 
                        p.reference as refArticle, p.product_region as regionArticle, /* as nomDomaine,*/ od.product_name as libelleCompletProduit,
                        od.product_quantity as quantiteCommande, od.unit_price_tax_excl as puHT, od.total_price_tax_excl as montantLigneHtNet,
                        
                        fvl.value as nomAppellation, 
                        fvl.value as couleur, 
                        
                        od.original_product_price as prixAchatArticle, od.original_wholesale_price as montantLigneAchatNet
                                
                
from ps_order_detail od

LEFT JOIN ps_orders o ON(od.id_order = o.id_order)
LEFT JOIN ps_order_invoice oi ON(od.id_order = oi.id_order)
LEFT JOIN ps_address a ON(o.id_address_delivery = a.id_address)
                    
LEFT JOIN ps_product p ON(p.id_product = od.product_id)
LEFT JOIN ps_feature_product fp on od.product_id = fp.id_product
LEFT JOIN ps_feature_value_lang fvl on fp.id_feature_value = fvl.id_feature_value    

LEFT JOIN ps_product_lang pl ON(pl.id_product = p.id_product)
LEFT JOIN ps_customer c ON(o.id_customer = c.id_customer)
LEFT JOIN ps_country_lang cl ON(a.id_country = cl.id_country)
LEFT JOIN ps_customer_group cg ON(c.id_customer = cg.id_customer)
LEFT JOIN ps_group_lang gl ON(cg.id_group = gl.id_group)              
                         
                          where nomAppelation in (select fvl.value from ps_feature_value_lang fvl where fp.id_feature = 197)
                          AND couleur in (select fvl.value as couleur from ps_feature_value_lang fvl where fp.id_feature = 133)
                          
                          AND o.valid = 1
                    AND DAY(o.date_add) = DAY(NOW() - INTERVAL 1 DAY)
                    AND MONTH(o.date_add) = MONTH(NOW())
                    AND YEAR(o.date_add) = YEAR(NOW())
                    GROUP BY o.id_order