SQL requette bug

[Anthony]

Bonjour tous le monde je suis actuellement en train de faire une requête pour récupérer les différente ventes ainsi que les produit associé par catégorie.

Mais je n'arrive pas à récupérer les catégories... pouvez-vous m'aider svp.

Il me faut juste la catégorie...

SELECT d.id_order, os.name AS Paiement, o.date_upd AS Date, d.product_name AS Produit, d.product_reference AS Ref, d.product_quantity AS Qty,  d.product_price AS Prix, o.payment,  CONCAT_WS(  ' ', g.firstname, g.lastname ) AS Client, CONCAT_WS(' ', ai.address1, ai.address2,  ai.postcode, ai.city, ai.other, ai.phone, ai.phone_mobile) AS Adresse, g.email
FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 
LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE os.id_lang =1
AND o.date_upd > (NOW() - INTERVAL 1 WEEK)
GROUP BY d.id_order, d.product_name
ORDER BY d.id_order DESC

Merci d'avance 

[doekia]

ajoute un jointure sur ps_product (id_product) et une jointure sur ps_category_lang avec id_category = p.id_category_default

et pense pour des raison de performances à transformer les left join unique en inner join

et la clause os.id_lang va dans le on de os

[Anthony]

Bonjour, 

Merci de ta réponse, j'essaye une jointure mais j'ai une erreur... 

SELECT d.id_order, os.name AS Paiement, o.date_upd AS Date, d.product_name AS Produit, d.product_reference AS Ref, d.product_quantity AS Qty,  d.product_price AS Prix, o.payment,  CONCAT_WS(  ' ', g.firstname, g.lastname ) AS Client, CONCAT_WS(' ', ai.address1, ai.address2,  ai.postcode, ai.city, ai.other, ai.phone, ai.phone_mobile) AS Adresse, g.email
FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 
LEFT JOIN ps_category_lang cl ON (p.id_category_default)
LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE os.id_lang =1
AND o.date_upd > (NOW() - INTERVAL 1 WEEK)
GROUP BY d.id_order, d.product_name
ORDER BY d.id_order DESC

[doekia]

elle est où la table ps_product? désolé mais en général tous les mots d'une phrase ont leur importance:

Quote

ajoute un jointure sur ps_product (id_product) et une jointure sur ps_category_lang avec id_category = p.id_category_default

 

[Anthony]

17 hours ago, doekia said:

elle est où la table ps_product? désolé mais en général tous les mots d'une phrase ont leur importance:

 

 

Je fais les deux jointures mais je me retrouve face à une erreur.

#1054 - Champ 'ps_product.id_product' inconnu dans on clause

 

SELECT d.id_order, os.name AS Paiement, o.date_upd AS Date, d.product_name AS Produit, d.product_reference AS Ref, d.product_quantity AS Qty,  d.product_price AS Prix, o.payment,  CONCAT_WS(  ' ', g.firstname, g.lastname ) AS Client, CONCAT_WS(' ', ai.address1, ai.address2,  ai.postcode, ai.city, ai.other, ai.phone, ai.phone_mobile) AS Adresse, g.email
FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 
LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
LEFT JOIN ps_product p ON ( d.product_id = ps_product.id_product )
LEFT JOIN ps_category_lang cl ON ( cl.id_category = p.id_category_default )
WHERE os.id_lang =1
GROUP BY d.id_order, d.product_name
ORDER BY d.id_order DESC

 

Pourquoi cette erreur ? 

[Anthony]

14 minutes ago, Anthony said:

 

Je fais les deux jointures mais je me retrouve face à une erreur.

#1054 - Champ 'ps_product.id_product' inconnu dans on clause

 

SELECT d.id_order, os.name AS Paiement, o.date_upd AS Date, d.product_name AS Produit, d.product_reference AS Ref, d.product_quantity AS Qty,  d.product_price AS Prix, o.payment,  CONCAT_WS(  ' ', g.firstname, g.lastname ) AS Client, CONCAT_WS(' ', ai.address1, ai.address2,  ai.postcode, ai.city, ai.other, ai.phone, ai.phone_mobile) AS Adresse, g.email
FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 
LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
LEFT JOIN ps_product p ON ( d.product_id = ps_product.id_product )
LEFT JOIN ps_category_lang cl ON ( cl.id_category = p.id_category_default )
WHERE os.id_lang =1
GROUP BY d.id_order, d.product_name
ORDER BY d.id_order DESC

 

Pourquoi cette erreur ? 

C'est bon en fait ça fonctionne, 

Merci pour tout

SELECT d.id_order, os.name AS Paiement, o.date_upd AS Date, d.product_name AS Produit, cl.name AS Categorie, d.product_reference AS Ref, d.product_quantity AS Qty,  d.product_price AS Prix, o.payment,  CONCAT_WS(  ' ', g.firstname, g.lastname ) AS Client, CONCAT_WS(' ', ai.address1, ai.address2,  ai.postcode, ai.city, ai.other, ai.phone, ai.phone_mobile) AS Adresse, g.email
FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 
LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
LEFT JOIN ps_product p ON ( d.product_id = p.id_product )
LEFT JOIN ps_category_lang cl ON ( cl.id_category = p.id_category_default )
WHERE os.id_lang =1
GROUP BY d.id_order, d.product_name
ORDER BY d.id_order DESC