CODEWITHSUNDEEP
oracle-database
user4618954
user4618954

Reputation: 37

Oracle Query to find missing value

I am struggling with the following. I am looking to retrieve all the identifiers that do not have a specific value.

Data example

Unique_ID    Country
---------    -------
1            USA
1            CDN
2            USA
2            CDN
3            USA
4            USA
4            CDN
....         ....

The unique ID goes to 120,000. I would be looking to return all Unique_ID values that dont have CDN. This is probably super simple. I am hoping for some help.

Upvotes: 1

Views: 1153

Answers (5)

Steve
Steve

Reputation: 31

select unique_id from your_table t where country <> 'CDN' and not exists (select 1 from your_table where country = 'CDN' and unique_id = t.unique_id)

Upvotes: 0

MT0
MT0

Reputation: 167982

Oracle Setup:

CREATE TABLE table_name ( Unique_ID, Country ) AS
SELECT 1, 'USA' FROM DUAL UNION ALL
SELECT 1, 'CDN' FROM DUAL UNION ALL
SELECT 2, 'USA' FROM DUAL UNION ALL
SELECT 2, 'CDN' FROM DUAL UNION ALL
SELECT 3, 'USA' FROM DUAL UNION ALL
SELECT 5, 'USA' FROM DUAL UNION ALL
SELECT 5, 'CDN' FROM DUAL;

Query 1 - If there will always be a unique_ID with a different country:

SELECT unique_ID
FROM   table_name
GROUP BY unique_ID
HAVING   COUNT( CASE Country WHEN 'CDN' THEN 1 END ) = 0;

Output:

 UNIQUE_ID
----------
         3

Query 2 - If there can be no entries for that Unique_ID even for different countries:

SELECT  i.Unique_ID
FROM    ( SELECT LEVEL AS Unique_ID
          FROM   DUAL
          CONNECT BY LEVEL <= 5 -- Maximum ID value
        ) i
        LEFT OUTER JOIN
        table_name  t
        ON ( i.Unique_ID = t.Unique_ID AND t.Country = 'CDN' )
WHERE   t.Unique_ID IS NULL;

Output:

 UNIQUE_ID
----------
         4 
         3

Upvotes: 1

user5683823
user5683823

Reputation: 

select Unique_ID from table_name
minus
select Unique_ID from table_name where Country = 'CDN'

Upvotes: 0

Ivan Gritsenko
Ivan Gritsenko

Reputation: 4236

Solution #0:

select Distinct Unqiue_ID 
from table_name
where Unqiue_ID not in (select Unqiue_ID 
                         from table_name
                         where Country = 'CDN')

Solution #1:

select t1.Unqiue_ID
from table_name t1
left join table_name t2
on t1.Unqiue_ID = t2.Unqiue_ID and t2.Country = 'CDN'
where t2.Unqiue_ID is null

Upvotes: 0

Mihai
Mihai

Reputation: 26784

You need post aggregation filtering

SELECT Unique_ID 
FROM T 
GROUP BY Unique_id 
HAVING SUM(CASE WHEN Country='CDN' THEN 1 ELSE 0 END)=0

Upvotes: 1

Related Questions