Question: I have two tables (table A) and (table B) and one table variable (@country) that will build the where condition for finding out the list of employees matching the countries.

**TableA(empId, name)**

(1,John),(2,Mary),(3,Harry)

**TableB(empId, country)**

(1,Australia),(1,US),(1,UK),(2,US)

For example, I need to select only those employees from TableA who have resides in both Australia and US. i.e. emp 1 (John). The query should be able to handle more countries in where clause if require. This depends on the number of countries in table variable @country. 

I have tried many option including the following query but nothing seems to work.

```
 DECLARE @country TABLE (
        [country]   [nvarchar](255) NOT NULL
 );

 insert into @country (country) values('Australia'),('US')

Select E.empID, EC.empID,EC.country from TableA E
INNER JOIN  TableB EC on E.empID= EC.empID
Where EC.country = ALL(Select country from @country)

```

Could you please advise on how to write the best query to achieve this task? Please note that @country can have one or more countries.

Answer: 
Try:

```
SELECT E.empID
    ,EC.empID
    ,EC.country
FROM TableA E
INNER JOIN TableB EC
    ON E.empID = EC.empID
WHERE EXISTS (
        SELECT 1 
        FROM TableB EC_US
        WHERE EC_US.empID = EC.empID
            and EC_US.Country = 'US'
    )
    AND EXISTS (
        SELECT 1 
        FROM TableB EC_Aus
        WHERE EC_Aus.empID = EC.empID
            and EC_Aus.Country = 'Australia'
    )

```

Or:

```
SELECT E.empID
    ,EC.empID
    ,EC.country
FROM TableA E
INNER JOIN TableB EC
    ON E.empID = EC.empID
WHERE EC.empID IN (
        SELECT EC_Sub.empID
        FROM TableB EC_Sub
        WHERE EC_Sub.Country IN ('Australia','US')
        GROUP BY EC_Sub.empID
        HAVING COUNT(*) = 2
    )

```
```
Select E.empID, EC.empID,EC.country 
from TableA E INNER JOIN TableB EC on E.empID= EC.empID
Where EC.country IN ('Australia','US');

```