Posted by Erland Sommarskog on 01/28/07 10:31

DE (ooff@hotmail.com) writes:
> Suppose I have two tables. Each of them has a single column, named as
> c1. For table T1, I have:
> 1
> 1
> 1
> 3
> 3
> 5
> 7
> 9
> For table T2, I have:
> 1
> 2
> 3
> 4
> 5
> 1
> 3
> The exercise I want to do is to select the number of occurence in T1
> for those elements in T2. For above tables, I want to show:
> 1 3 ( i.e. "1" is in T2 and shows 3 times in T1)
> 2 0 (i.e. "2" is in T2 but doesn't show in T1)
> 3 2 (i.e. "3" is in T2 and show 2 times in T1)

SELECT T2.c1, coalesce(COUNT(T1.c1), 0)
FROM T2
LEFT JOIN T1 ON T2.c1 = T1.c1
GROUP BY T2.c1

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]