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 Now, added where clause. Change any value of @country and execute the query:

```
DECLARE @TableA TABLE (empId INT, [Name] VARCHAR(100))

INSERT INTO @TableA VALUES (1, 'John')
INSERT INTO @TableA VALUES (2, 'Mary')
INSERT INTO @TableA VALUES (3, 'Harry')

DECLARE @TableB TABLE (empID INT, country VARCHAR(100))

INSERT INTO @TableB VALUES (1, 'Australia')
INSERT INTO @TableB VALUES (1, 'UK')
INSERT INTO @TableB VALUES (2, 'US')

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

 INSERT INTO @country (country) VALUES('Australia'),('US')

SELECT a.* , tb.country
FROM   @TableA AS a
       INNER JOIN (
                SELECT b.empid,
                       COUNT(*)  AS empInMultipleCountry
                FROM   @TableB      b   
                GROUP BY
                       empid
            ) b
            ON  a.empId = b.empid
     INNER JOIN @TableB AS tb
            ON tb.empId = a.empId

WHERE  empInMultipleCountry > 1
AND EXISTS (SELECT 1 FROM @country AS c WHERE c.country = tb.country)

```
```
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');

```