duckmike duckmike - 4 years ago 68
SQL Question

SQL JOIN on all

I have a table, table A

Key String
1 Val1
1 Val2
2 Val1
3 Val1
3 Val2


that I'd like to join on table B

Key String
1 Val1
2 Val2


where I'd like to get only values in table A that match to all of the contents of table A. The result in this example would give me keys 1 & 3 from table. Those are the only records where there is an exact match with table B.

How do I write that SQL, using SQL Server?

Answer Source

Try this:

SELECT a.Key
FROM tableA AS a
JOIN tableB AS b ON a.Key = b.Key AND a.String = b.String
GROUP BY a.Key
HAVING COUNT(DISTINCT a.String) = (SELECT COUNT(DISTINCT(String)) 
                                   FROM TableB)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download