SQL Query to fetch the customers registered in the DB without email address(CS can have phonenumber-CodePudding

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.

CodePudding user response：

I suggest that you use the following query, modified with the exact column and table name:

SELECT * FROM table_name
WHERE COALESCE( value_field ,'') NOT LIKE '%@%';

This will return all rows where there is not an @ in the column. This is a very minimum check that there is an email adresse and not a telephone number or other note.
Of course there could be an @ and it not be a valid e-mail adresse. We could go further with regex etc. but this is a very basic test.

CodePudding user response：

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.