request for customers who bought product no longer available

[ventesites]

Hello

 

I am trying to get the list of customers who bought specific products no longer available

 

I got this request from the forum

 

SELECT C.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 IN (3,45,47,94,102)

 

but it works only for available product ...(available_for_order=1)

 

How can i modify the request to get the result for products no longer available, sold out? (available_for_order=0)

 

Thanks

 

Pat

Edited September 24, 2016 by ventesites (see edit history)

[shokinro]

you can try this one 

 

 

SELECT C.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
INNER JOIN ps_product p on od.Product_ID = p.id_product

WHERE p.available_for_order = 0

[ventesites]

I can't get the above requests to work so i tried using category

 

SELECT C.email, C.lastname, C.firstname, P.id_product, PL.name
FROM ps_customer C, ps_product_lang PL
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_product P on OD.product_id = P.id_product
WHERE P.available_for_order = 0
AND P.id_category_default = 2246

 

It gave me "#1054 - Unknown column 'C.id_customer' in 'on clause'...

 

what am i doing wrong?
 

[shokinro]

Your statement are using different table join.

 

Please try this

 

SELECT C.email, C.lastname, C.firstname, P.id_product, PL.name

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_product P on OD.product_id = P.id_product

INNER JOIN ps_product_lang PL ON PL.id_product = P.id_product

WHERE P.available_for_order = 0

AND P.id_category_default = 2246

[ventesites]

now it's working with your help, thanks so much to every one for your input

[shokinro]

glad it worked and helped, and thanks for feedback.