Hi,

New to SQL. Got some questions about it.

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)

It seems I can't figure out a good way to do this. Any help will be
appreciated.

Thanks

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I will give you a different solution for your homework, so you will
still have to think for yourself (which should I take? and why?):

SELECT c1, COUNT(*)
FROM T1
GROUP BY c1

UNION ALL

SELECT DISTINCT c1, 0
FROM T2
WHERE NOT EXISTS (
SELECT *
FROM T1
WHERE T1.c1 = T2.c1
)
ORDER BY c1

HTH,
Gert-Jan


DE wrote:
>
> Hi,
>
> New to SQL. Got some questions about it.
>
> 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)
>
> It seems I can't figure out a good way to do this. Any help will be
> appreciated.
>
> Thanks