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.
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
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
select Distinct Unqiue_ID
from table_name
where Unqiue_ID not in (select Unqiue_ID
from table_name
where Country = 'CDN')
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
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)
