|
|
Posted by Gert-Jan Strik on 01/28/07 12:24
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
Navigation:
[Reply to this message]
|