'SQL query to get records with multiple occurance of specific conditions

Hope someone can help me with this. I am not great at SQL.

I want to get list of employees who has address in both USA and UK(England/Scotland)

Employee Address
Emp1 England
Emp1 England
Emp1 Scotland
Emp2 US
Emp2 US
Emp2 Canada
Emp3 Scotland
Emp3 US
Emp3 Scotland
Emp3 US
Emp3 India

There can be multiple countries but i want to focus on US and UK(which includes both England and Scotland) only.

sqlsql-server


Solution 1:[1]

You can join the table with itself (one instance for US, one instance for UK) to find rows you want. For example:

select distinct us.employee
from t us
join t uk on uk.employee = us.employee
where us.address = 'US'
  and uk.address in ('England', 'Scotland')

Alternatively, you can use EXISTS to filter out rows.

Solution 2:[2]

Using aggregation this is pretty straight forward.

Select Employee
from YourTable
where Address in ('England', 'Scotland')
group by Employee
having count(distinct Address) = 2

Solution 3:[3]

You can do this effectively by using a case that identifies every grouped nationality you need, then applying a COUNT DISTINCT, that does not take into account of nulls in the nationality column.

SELECT 
    Employee,
    Address,
    (CASE WHEN Address IN ('England', 'Scotland') THEN 'english'
          WHEN Address IN ('US')                  THEN 'american'
          ELSE NULL
    END) AS nationality
FROM
    table   
GROUP BY 
    Employee
HAVING
    COUNT(DISTINCT nationality) = 2

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 The Impaler
Solution 2 Sean Lange
Solution 3 lemon

Related Questions