Search code examples
mysqlprestashop

How to select customers that have ordered 3 diferrent product, but not a fourth?

I have this SQL query that let me know when the customer have ordered a certain product :

SELECT * FROM ps_customer c
INNER JOIN ps_orders o ON (c.id_customer=o.id_customer)
INNER JOIN ps_order_detail od ON(od.id_order=o.id_order)
WHERE od.product_id=1

I'd like to get the customers that have order product 1, 2, 3, but never 4.

I can't simply make WHERE od.product_id IN (1, 2, 3) AND od.product_id != 4, but I'm stuck on how to build that query correctly.

For information, the database structure is a basic Prestashop (1.6.1.4) datastore.

Here's the SQLFiddle : http://sqlfiddle.com/#!9/dfd65

(I can't add the data, it's too much for SQLFiddle ...)

Solution

  • may be you should think differently 

    SELECT * FROM ps_customer c
INNER JOIN ps_orders o ON (c.id_customer=o.id_customer)
INNER JOIN ps_order_detail od ON(od.id_order=o.id_order)
WHERE od.product_id IN (1, 2, 3)  and c.id_customer NOT IN (select id_customer from ps_orders where product_id IN (4) )