Jump to content

gestionnaire SQL et détails commande


Recommended Posts

Bonjour je souhaite faire deux requete sql sur PS 1.5 mais je m'y connais pas assez en base sql pour le faire seul :

- extraire mes commandes mais avec le détails de la commande (c'est a dire que je voudrais aussi extraire l'id et les noms des produits achetés

J'ai cette commande  sql mais manque détails produit (ID et nom) :

SELECT o.`id_order` AS `id`,
    CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`,
    ca.`name` AS `Carrier`,
    cu.`name` AS `Currency`,
    o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`,
    o.`date_add` AS `Date`
FROM `ps_orders` o
LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN `ps_carrier` ca ON (o.id_carrier = ca.id_carrier)
LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)

- extraire le nombre de commande pour tout les id produit 

 

Merci d'avance.

Share this post


Link to post
Share on other sites


SELECT o.`id_order` AS `id`,

CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`,

ca.`name` AS `Carrier`,

cu.`name` AS `Currency`,

o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`,

o.`date_add` AS `Date`,

od.`product_id`, od.`product_name`

FROM `ps_orders` o

LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)

LEFT JOIN `ps_carrier` ca ON (o.id_carrier = ca.id_carrier)

LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)

LEFT JOIN `ps_order_detail` od ON (o.`id_order` = od.`id_order`)

Share this post


Link to post
Share on other sites

  • 1 month later...
SELECT o.`id_order` AS `id`,
    CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`,
    ca.`name` AS `Carrier`,
    cu.`name` AS `Currency`,
    o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`,
    o.`date_add` AS `Date`,
    od.`product_id`, od.`product_name`
FROM `ps_orders` o
LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN `ps_carrier` ca ON (o.id_carrier = ca.id_carrier)
LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)
LEFT JOIN `ps_order_detail` od ON (o.`id_order` = od.`id_order`)
WHERE o.`date_add`>'2014-01-01 00:00:00'

Je ne l'ai pas testée.

Share this post


Link to post
Share on other sites

  • 2 years later...

Bonjour à tous,

 

Si quelqu'un pouvait m'aider à ajouter l' attribute_id à la requête ci-dessous ça m'aiderait énormément à sortir des stats de ventes......

 

Merci d'avance :D

 

SELECT o.`id_order` AS `id`,
CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`,
ca.`name` AS `Carrier`,
cu.`name` AS `Currency`,
o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`,
o.`date_add` AS `Date`,
od.`product_id`, od.`product_name`
FROM `ps_orders` o
LEFT JOIN
`ps_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN `ps_carrier` ca ON (o.id_carrier = ca.id_carrier)
LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)
LEFT JOIN `ps_order_detail` od ON (o.`id_order` = od.`id_order`)
WHERE o.`date_add`>'2014-01-01 00:00:00'

Share this post


Link to post
Share on other sites

  • 4 months later...
  • 1 month later...
  • 8 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More