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

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 Table1: 1, 2, 5, 5, 6, 7

And I insert the following values in Table2: 3, 4, 5, 5, 8, 9

.

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.

.

EDIT: I should have made this clearer, I'm interested in any common values, not just duplicates. The thing is that duplicates are harder to deal with.

So if I insert the following values in Table1: 1, 2, 5, 5, 5, 6, 7, 8

And I insert the following values in Table2: 3, 4, 5, 5, 5, 6, 9, 0

Then I want to see 5, 5, 5, 6 as the common terms.

Answers


DECLARE @Table1 TABLE (Term1 int)
INSERT INTO @Table1 VALUES (1),(2),(5),(5),(5),(6),(7),(8)
DECLARE @Table2 TABLE (Term2 int)
INSERT INTO @Table2 VALUES (3),(4),(5),(5),(5),(6),(9),(0)

;WITH T1 AS
(
SELECT Term1, ROW_NUMBER() OVER (PARTITION BY Term1 ORDER BY (SELECT 0)) RN1
FROM @Table1
),
T2 AS
(
SELECT Term2, ROW_NUMBER() OVER (PARTITION BY Term2 ORDER BY (SELECT 0)) RN2
FROM @Table2
)
SELECT T1.Term1
FROM T1 JOIN T2 ON T1.Term1=T2.Term2 AND T1.RN1=T2.RN2

Output

Term1
-----------
5
5
5
6

Need Your Help

How do you find variable and function definitions in Anjuta?

c++ linux ide gnome anjuta

Is Anjuta capable of telling you where a variable or function is first defined?

MySQL: update statement

mysql sql sql-update

What I want to do is compare two tables, and perform an update in tableb where column doesn't match the same column.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.