select only those who have no contact

user_16957404 0
Viewed 50

I updated my question.

I have table like this:

id name contact
1 A 65489
1 A
1 A 45564
2 B

so, i want table like this:

id name contact
2 B
oracle sql
3 Answers

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.

user_18632290

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

user_15092640

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)
user_48081220
Related