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)

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

CodePudding user response：

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.

CodePudding user response：

Using aggregation this is pretty straight forward.

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

CodePudding user response：

You can make use of IN to find out the employees who has address in both US and UK as below.

declare @tbl table(emp varchar(20),address varchar(20))

insert into @tbl
values('Emp1','England'),('Emp1','Scotland'),('Emp2','US'),('Emp3','US')
,('Emp3','Scotland')

select emp 
from 
@tbl where address = 'US'
and emp in 
(
select emp
from 
@tbl
where 
address = 'Scotland' or address = 'England')

CodePudding user response：

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