SQL Query for New Clients/Visitors

[montetoni]

My problem is related to new and old customers.

If a customer makes an order as a visitor he is always a new customer according to how the prestashop works.

There is a field called “New Client*”  that indicates if the order has been made by a new client. That field is set to either yes or no depending if the customer has made zero or more orders.

My thought is the same visitor has the same mobile phone and therefore, to somehow make an sql query so that if the delivery phone is not a new unique number in the database then the customer is not new.   

My goal is not to have to call customers if they have previously made an order to the shop.

So what sql query do I write so that the field “New Client*” displays Yes only if the phone number is in not the database?

Any other way/solution to achieve my goal is appreciated.

[EvaF]

current sql looks like

        $this->_select = '
		a.id_currency,
		a.id_order AS id_pdf,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS `customer`,
		osl.`name` AS `osname`,
		os.`color`,
//----------------- change this line -----------------------------
		IF((SELECT so.id_order FROM `' . _DB_PREFIX_ . 'orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new,
//----------------- change this line -----------------------------
		country_lang.name as cname,
		IF(a.valid, 1, 0) badge_success';

        $this->_join = '
		LEFT JOIN `' . _DB_PREFIX_ . 'customer` c ON (c.`id_customer` = a.`id_customer`)
		INNER JOIN `' . _DB_PREFIX_ . 'address` address ON address.id_address = a.id_address_delivery
		INNER JOIN `' . _DB_PREFIX_ . 'country` country ON address.id_country = country.id_country
		INNER JOIN `' . _DB_PREFIX_ . 'country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = ' . (int) $this->context->language->id . ')
		LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON (os.`id_order_state` = a.`current_state`)
		LEFT JOIN `' . _DB_PREFIX_ . 'order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = ' . (int) $this->context->language->id . ')';

the changed line  could look something like that:
 

IF((SELECT so.id_order FROM `' . _DB_PREFIX_ . 'orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 
  IF((SELECT COUNT(pa.id_customer) 
     FROM `' . _DB_PREFIX_ . 'orders` sop 
     INNER JOIN `' . _DB_PREFIX_ . 'address` pa   ON pa.id_address = sop.id_address_delivery  
     WHERE sop.id_order < a.id_order 
           AND  ((pa.phone<>'' AND (address.phone = pa.phone OR address.phone_mobile= pa.phone)) OR 
                 (pa.phone_mobile<>'' AND (address.phone= pa.phone_mobile OR address.phone_mobile = pa.phone_mobile)))) > 0 , 0, 1)) as new,

 

[montetoni]

Works like a charm. 

Thanks a lot