Getting number of common terms in two tables where there are duplicates.

Programmer Question

Consider the following two tables in SQL Server 2008:

Table1 which has just one int field: Term1

Table2 which also just has one int field: Term2. (Yeah, I know - I lack imagination.)

.

Let's say that I insert the following values in Term1: 1, 2, 5, 5, 6, 7

And I insert the following values in Term 2: 3, 4, 5, 5, 7, 8

.

I want to run a query where I find the terms that are common to both tables, and I want these paired off. So when there are duplicates (as there are in this case - 5 and 5), I want there to be a single row for each pair found. In other words, I would like two pairs of 5s returned as the two terms that are common.

How do I do this? If I do a simple inner join on Table1 and Table2, I get four pairs of 5's returned.

Find the answer here