Export list of customers who bought a specific product

[Do You Speak Graphic]

Hello everyone,

 

I need to export a list of all the customers (with e-mail addresses) who purchased a specific product.

 

I've tried some queries without success.

 

Anybody can help?

 

 

Thanks in advance,

 

 

--R

[razaro]

Go to Advanced Parameters >  SQL Manager in back office and add following SQL. 

Note it uses ps_ as prefix but you may have different, and it is for product with id 100 

in this example.

SELECT DISTINCT c.`id_customer`, CONCAT(c.`firstname`,', ', c.`lastname`) as customer, c.`email` 
FROM `ps_customer` c LEFT JOIN `ps_orders` o ON (c.`id_customer` = o.`id_customer`) LEFT JOIN `ps_order_detail` od ON o.`id_order` = od.`id_order`
WHERE od.`product_id` = 100

Then you can view or export results.

[Do You Speak Graphic]

Thank you so much for your kind help. It works like a charm...

[GMM148]

This is a great little query, but I'd like to take it one stage further if you can assist? Thanks in advance if you can

 

What I'd like to achieve is a table like this - where id_state is the Order Status ('Delivered' etc). I've got the first three columns but 

id_customer	customer	email	qty	id_state
7	Ant	[email protected]	qty ordered	DELIVERED or any state (whichever is possible)

 

I've used the SQL from your suggestion to achieve the first three columns, but can't crack the Qty and Order Status - I am presuming that I need to use 'product_quantity' from ps_order_detail and id_order_state from ps_order_state, but if anyone can insert it correctly into the SQL query I have already that would be really appreciated. Thanks

ps_order_detail - product_quantity

I want to show the number ordered by all customers for the product_id specificied

ps_order_state - id_order_state

 

SELECT DISTINCT c.`id_customer`, CONCAT(c.`firstname`,' ', c.`lastname`) as customer, c.`email` 
FROM `ps_customer` c LEFT JOIN `ps_orders` o ON (c.`id_customer` = o.`id_customer`) LEFT JOIN `ps_order_detail` od ON o.`id_order` = od.`id_order`
WHERE od.`product_id` = 8

 

ps_order_detail - product_quantity

I want to show the number ordered by all customers for the product_id specificied

ps_order_state - id_order_state

[GMM148]

Can anyone please assist? Thanks in advance

[DataKick]

Something like this?

SELECT 
  c.id_customer, 
  CONCAT(c.firstname,' ', c.lastname) as customer, 
  c.email,
  SUM(od.product_quantity) as `qty`,
  s.name
FROM ps_customer c 
LEFT JOIN ps_orders o ON (c.id_customer = o.id_customer) 
LEFT JOIN ps_order_detail od ON (o.id_order = od.id_order)
LEFT JOIN ps_order_state_lang s ON (o.current_state = s.id_order_state AND s.id_lang = 1)
WHERE od.product_id = 1
GROUP BY o.id_order, od.product_id, c.id_customer, s.name;

 

[stech]

Is possible to have a column with order id or not?