Requete SQL - Feature product

[Tomi_Douma]

Bonjour, 

Voici ci-dessous une requête SQL dans laquelle j'essaie de récupérer plusieurs valeur en faisant des jointures entre tables. En retirant les features aucun soucis ça marche, pareil si je n'ai qu'une seul entrée ça marche même avec les features, mais dès que j'ai plusieurs commandes pour une même date, je reçois l'erreur suivante :

Subquery returns more than 1 row

Quelqu'un aurait une solution? 

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,
								(select fvl.value from ps_order_detail od
									LEFT JOIN ps_orders o ON(od.id_order = o.id_order)						
									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	
																	
									where fp.id_feature = 197 
									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) as nomAppellation,
		                     
								(select fvl.value from ps_order_detail od
								
									LEFT JOIN ps_orders o ON(od.id_order = o.id_order)						
									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	
																	
									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) as couleur,
		                     
								(select fvl.value from ps_order_detail od
									
									LEFT JOIN ps_orders o ON(od.id_order = o.id_order)						
									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	
																	
									where fp.id_feature = 134 
									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) as mil,
								
								/*(select fvl.value from ps_order_detail where fp.id_feature = 197) as degre,*/
								(select fvl.value from ps_order_detail od
									
									LEFT JOIN ps_orders o ON(od.id_order = o.id_order)						
									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	
																	
									where fp.id_feature = 141
									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) as centilisation,
		                     
									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_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 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

 

[Tomi_Douma]

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

 

Edited October 29, 2019 by Tomi_Douma (see edit history)