Prestashop / SQL Query from SQL Database

Question

I am trying to create a query that returns the firstname, lastname, email and key_value for each order of a certain product (id 49) but I can't quite configure the SQL query correctly to get each key for each customer.

SELECT C.email,
C.firstname,
C.lastname,
K.key_value
FROM ps_customer C 
INNER JOIN ps_orders O on C.id_customer = O.id_customer
INNER JOIN ps_order_detail OD on O.id_order = OD.id_order
INNER JOIN ps_keymanager OD on O.id_order = OD.id_order
WHERE OD.product_id =49

Answer 1

you could try this.. But!.

I've no idea what the ps_keymanager is, but if your using it im sure you can fit into the query below.. ;)

SELECT O.reference AS ORDERREF, C.firstname, C.lastname, C.email AS Email
FROM ps_customer C 
INNER JOIN ps_orders O on C.id_customer = O.id_customer
INNER JOIN ps_order_detail OD on O.id_order = OD.id_order
WHERE OD.product_id = 846

This will list

Order Reference | Firstname | Surname | Email

WHERE product_id = (in my case 846)

Answer 2

I share the consultation that I use to obtain different information from the client, by order id :

SELECT pod.id_order, c.id_customer, 
  CONCAT(c.firstname, c.lastname) AS client, 
  c.firstname, c.lastname, pa.address1, pa.address2, 
  pa.city, pa.id_country,c.email, pa.postcode, pa.phone_mobile, 
  pa.phone, pc.iso_code AS country
FROM ps_orders po 
  LEFT JOIN ps_order_detail pod  ON (po.id_order = pod.id_order) 
  LEFT JOIN ps_product pp ON (pod.product_id = pp.id_product) 
  LEFT JOIN ps_customer c ON (c.id_customer = po.id_customer) 
  LEFT JOIN ps_address pa ON (pa.id_customer = c.id_customer) 
  LEFT JOIN ps_country pc ON (pc.id_country = pa.id_country) 
WHERE pod.id_order = 34549

You can add or remove the columns you need in your sql query.