CODEWITHSUNDEEP
mysqlprestashop
Cyril N.
Cyril N.

Reputation: 39889

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 ...)

Upvotes: 0

Views: 80

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

you could use a couple of dinaminc table join on id_customer 

  select * from (  
        SELECT id_customer 
        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  <> 4  ) t1
  INNER JOIN (
    SELECT id_customer 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)
    having count(distinct(od.product_id)) =3
    ) t2 on t1.id_customer = t2.id_customer

Upvotes: 1

krishn Patel
krishn Patel

Reputation: 2599

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) )

Upvotes: 1

Related Questions