How to Select rows in a table with all matching conditions in joining table

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.

@kbball I need country because it will contain all the countries needs to be searched. In my question I have taken example of Australia and US for the sake of simplicity but in reality it can be 1 or more countries. — akajain, Oct 17 '17 at 04:09
Possible duplicate of [How to filter SQL results in a has-many-through relation](https://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) — Oleg, Oct 17 '17 at 04:41
@Oleg, If you go through the link you have posted, you will find that it only works on two items in where condition (soccer and baseball) which is quite straight forward to do. But in my question the @ country is populated dynamically. It is not necessarily limited to two countries. I have mentioned in question that it can be more. — akajain, Oct 23 '17 at 04:15
Nope, many solutions there that work on as much items as you want. — Oleg, Oct 23 '17 at 05:54

score 0 · Answer 1 · answered Oct 17 '17 at 03:59

0

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
    )

answered Oct 17 '17 at 03:59

Bacon Bits

30,782
5
59
66

You are going to have duplicate rows come back in that first query - one where EC.country is US and one where it's Australia. Not sure which one the OP wants or why country was included in the sample query. – kjmerf Oct 17 '17 at 04:02
@kbball In the absence of desired output, I chose to follow the query he posted. This may not be the result he wants, but it is the result he has implicitly asked for. If you have a different interpretation or answer, you are encouraged to post your own answer. – Bacon Bits Oct 17 '17 at 04:04
@Bacon how about if @ country have more countries? Your query failed there. – akajain Oct 23 '17 at 04:15

Khorshed Alam · Answer 2 · 2017-10-23T06:36:04.997

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)

Not answering the original question. where is the "where" clause? — akajain, Oct 23 '17 at 03:55

score -1 · Answer 3 · answered Oct 17 '17 at 03:29

-1

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

answered Oct 17 '17 at 03:29

user8787713

1

1

Nope, this will select empId 2 (Mary) as well. I need who have both 'Australia and US' as country – akajain Oct 17 '17 at 03:32

How to Select rows in a table with all matching conditions in joining table

3 Answers3