SQL Query to fetch the customers registered in the DB without email address(CS can have phonenumber and email in the same field but duplicating)

I need help with writing this query please , in the Database – the customer is registered twice , one row with the email address in the VALUE field and the other row with phone number in the SAME VALUE field . I want to fetch customer who DO NOT HAVE email address in the VALUE FIELD . For example , I want to fetch only the last row from the list shown in the figure I shared.

Appreciate your help!

I tried creating multiple SELECT queries , but still not getting the accurate values.

Answer

Without seeing table schemas or example data, I’m making an assumption here that you have a field that is common to both rows so you know the customer row with the email value and the customer row with the phone value are linked to the same customer. For the purposes of this example, I’m going to call that field “customer_number”.

I’d suggest a query that utilises an auxiliary statement like this:

WITH customers_with_emails AS (
  SELECT customer_number
  FROM customers
  WHERE customer_value LIKE '%@%'
)
SELECT *
FROM customers
WHERE customer_number NOT IN (
  SELECT customer_number 
  FROM customers_with_emails
);

This will return customers with phone numbers, who do not also have an email address.