SQL Query to retrieve customers who have not ordered yet

[camfab]

Hello,

 

I'm trying to retrieve via sql query the list of all customers which have not ordered yet (in order to give them a particular discount)

This is the code:

select c.id_customer, c.firstname, c.lastname, count(o.id_order) as ordercnt from ps_customer c
left join ps_orders o on c.id_customer = o.id_customer group by o.id_customer 

but this query report all customers with order count as 1 (also the customers which have not ordered yet...

Can you help me?
thank you !

[bellini13]

this should do it

SELECT c.id_customer, c.firstname, c.lastname FROM ps_customer c WHERE NOT EXISTS (SELECT id_order FROM ps_orders o where c.id_customer = o.id_customer) 

[camfab]

Thank you, with a little correction, your code solved the problem!

 

SELECT c.id_customer, c.firstname, c.lastname FROM ps_customer c WHERE (NOT EXISTS (SELECT id_order FROM ps_orders o where c.id_customer = o.id_customer) ) 

Edited March 14, 2017 by camfab (see edit history)