I updated my question because it seems that I formulated the question incorrectly, for which I apologize.

I have table like this:

I only want users who have no contact or the contact length is not five.

If the user has two or more contacts and the length of one of them is five and the rest is not, then such users should not be included in the table.

so,If the customer has at least one contact length of five, I do not want that.

so, i want table like this:

Can you halp me?

CodePudding user response：

Using exists logic we can try:

SELECT *
FROM yourTable t1
WHERE NOT EXISTS (SELECT 1 FROM yourTable t2
                  WHERE t2.id = t1.id AND t2.contact IS NOT NULL);

In plain English, the above query says to return any records for which we cannot find another record belonging to the same id group having a non NULL contact value.

Here is a demo of the query.

CodePudding user response：

You can use:

SELECT id,
       MAX(name) AS name
FROM   table_name
GROUP BY id
HAVING COUNT(contact) = 0;

Which, for the sample data:

CREATE TABLE table_name (id, name, contact) AS
SELECT 1, 'A', 3265489 FROM DUAL UNION ALL
SELECT 1, 'A', NULL    FROM DUAL UNION ALL
SELECT 1, 'A', 5645564 FROM DUAL UNION ALL
SELECT 2, 'B', NULL    FROM DUAL;

Outputs:

ID	NAME
2	B

Or, if you can have contact values that are strings padded with white-space then:

SELECT id,
       MAX(name) AS name
FROM   table_name
GROUP BY id
HAVING COUNT(CASE LENGTH(TRIM(contact)) WHEN 7 THEN 1 END) = 0;

db<>fiddle here

CodePudding user response：

Have you tried

SELECT * 
  FROM t1
  WHERE t1.contact IS NULL OR length(trim(t1.contact)) = 0

CodePudding user response：

Just as you would formulate it - select all IDs that have no (minus) contact

SELECT * FROM table_name WHERE ID in 
(select id from table_name
 minus
 select id from table_name where contact is not null)