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.
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
Term1 ----------- 5 5 5 6