Selecting rows from an SQL table where entries in the first column have unique values in the second-CodePudding

I have a SQL table countryTable structured as shown:

id | country
--- --------
 1 | US
 2 | GB
 2 | US
 3 | AU
 4 | AU
 5 | CA
 4 | US

and I want to select only rows where the id has a single corresponding country - in this case, the output would be

id | country
--- --------
 1 | US
 3 | AU
 5 | CA

since ids 2 & 4 map to (GB, US) and (AU, US) and not exclusively single countries.

So far, I've tried this query:

select * 
from countryTable
group by 1,2
having count(distinct country) = 1

However, the result includes the rows with ids mapping to multiple countries.

Any guidance would be greatly appreciated.

CodePudding user response：

Don't group by both ID and country, as that will create a separate group for each combination. Instead, group by ID, and use min(country) to retrieve the single country value for those IDs having just one country.

select id, min(country) country
from countryTable
group by id
having count(distinct country) = 1;

CodePudding user response：

You just need to check existence of rows with same id and a different country value, like this

SELECT * FROM data d
WHERE NOT EXISTS (
    SELECT 1 FROM data dd 
    WHERE dd.id = d.id AND dd.country != d.country
)

Output

id	country
1	us
3	au
5	ca

You can check a working demo here

CodePudding user response：

First you need to collect the unique ids. Your group by is grouping by both id and country and all of them are distinct as a pair. You can do this by doing a subquery or a temporary WITH query and and an inner join on the results.

with uniqueIds(id) as (select id from countryTable
group by id
having count(*)=1)
select countryTable.* from countryTable
inner join uniqueIds u on countryTable.id = u.id

Info on how to use WITH clause: https://www.geeksforgeeks.org/sql-with-clause/