SQL-Query for selects with multiple conditions

[Pluncker]

I'm playing around with SQL-Queries in Back Office (PS 1.6.1.24). The task is to select customers who ordered last year (at least 3 times OR at leaset xxx Euro) but have not made an order this year.

It would be great if an SQL professional could help!

Thanks!

[musicmaster]

I would try using EXISTS.

So from the top of my head you would get something like

SELECT c.id_customer FROM ps_customer c WHERE 

(EXISTS(SELECT count(*) AS ocount FROM ps_orders o2 WHERE o2.id_customer=c.id_customer AND date_add >= '2018-01-01' AND date_add < '2019-01-01' WHERE ocount>=3 )

OR EXISTS(SELECT SUM(totalpaid) opaid FROM ps_orders o2 WHERE o2.id_customer=c.id_customer AND date_add >= '2018-01-01' AND date_add < '2019-01-01'  WHERE opaid>1000)))

AND NOT EXISTS (SELECT null FROM ps_orders o1 WHERE o1.id_customer=c.id_customer AND date_add >= '2019-01-01')

 - - -- 

If the exists query don't work you might consider using HAVING instead of WHERE inside them.

 

[Pluncker]

Many thanks, @musicmaster,

I will try it tomorrow.

[Pluncker]

SELECT 
  c.id_customer 
FROM 
  ps_customer c 
WHERE 
  (
    (
      EXISTS (
        SELECT 
          count(*) AS ocount 
        FROM 
          ps_orders o2 
        WHERE 
          o2.id_customer = c.id_customer AND date_add >= '2018-01-01' AND date_add < '2019-01-01' 
        HAVING 
          ocount >= 3
      ) 
      OR EXISTS (
        SELECT 
          SUM(o2.total_paid) opaid 
        FROM 
          ps_orders o2 
        WHERE 
          o2.id_customer = c.id_customer AND date_add >= '2018-01-01' AND date_add < '2019-01-01' 
        HAVING 
          opaid > 1000
      )
    ) 
    AND NOT EXISTS (
      SELECT 
        null 
      FROM 
        ps_orders o1 
      WHERE 
        o1.id_customer = c.id_customer AND date_add >= '2019-01-01'
    )
  )

works fine! Thank you for a great support!

[Pluncker]

Dear @musicmaster: Could you please help me again with a further SQL-Select? SQL is not my joy 😉

I need to select customers who ordered a specific product (product_id) AND this order was made in the last xx days AND the customer didn't order anything afterwards.

The output should be: id_customer and date_add of this last order.

What I`ve made is:

SELECT d.id_order, o.date_add, c.id_customer
	FROM ps_order_detail d 
	LEFT JOIN ps_orders o ON (d.id_order = o.id_order) 
	LEFT JOIN ps_customer c ON (o.id_customer = c.id_customer) 
	WHERE  d.product_id = 38
	AND TO_DAYS(o.date_add) >= TO_DAYS(NOW())-60
	GROUP BY o.id_customer
	ORDER BY o.date_add

Which selects the orders of this particular product in the last 60 days. But how to filter customers, who didn't order anything afterwards???

It would be great if you could help me again!
Many thanks!

[musicmaster]

Hi Pluncker,

This is starting to look more like something for which you hire someone than a question for a forum.

I have explained you about EXISTS and you don't even try to use it.

Anyway, this looks really simple. You just need to look who ordered more than once in this period. So it becomes SELECT COUNT(*) AS ordercount. And then at the end HAVING ordercount =1

 

[Pluncker]

Ok, got it! Thank you for the suggestion, musicmaster. I rebuilt the code, using EXISTS and it works:

SELECT c.id_customer, c.id_gender, c.firstname, c.lastname, c.email, o.date_add, o.id_order
FROM ps_customer c
LEFT JOIN ps_orders o ON (c.id_customer = o.id_customer)

WHERE (
		EXISTS (
			SELECT count(*) AS ocount
			FROM ps_orders o
			WHERE o.id_customer = c.id_customer
			HAVING ocount = 1
			) 
		AND EXISTS (
			SELECT *
			FROM ps_orders o
			WHERE o.id_customer = c.id_customer AND TO_DAYS(date_add) >= TO_DAYS(NOW()) - 30
			) 
		AND EXISTS (
			SELECT *
			FROM ps_order_detail d
			WHERE o.id_order = d.id_order AND d.product_id = xx
			)
		)
GROUP BY c.id_customer
ORDER BY o.date_add

Unfortunately, the SQL-Manager does not accept comments ("--" or "/* */")...