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)

user_13289114 0
Viewed 158

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.

subquery sql-scripts union postgresql sql
2 Answers

I suggest that you use the following query, modified with the exact column and table name. We use string_agg to coalesce all the value fields for the same customer id and only show them if none of them contain an @ sign.

create table customers(
id int, 
name varchar(25),
value varchar(25));

insert into customers values
(1,'Mr A','1234'),
(1,'Mr A','a@b.c'),
(2,'Mr B','6789');

select
  id,
  max(name) "name",
  string_agg(value,', ')
from
  customers
group by
  id
having 
  string_agg(value,', ')  NOT LIKE '%@%';

id | name | string_agg
-: | :--- | :---------
 2 | Mr B | 6789

db<>fiddle here

Admin1

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.

user_90939290
Related