Counting across tables?

[sigmahawk]

I've been beating my head against this for a while now and I need some help.

I've got two tables. in Table1 i've got ColA and ColB; in Table2 i've got ColA, ColB, and ColC. Table1.ColA and Table2.ColB contain the same type of information. Table1.ColA is unique, Table2.ColB is not. What query would tell me how many times each item in Table1.ColA appears in Table2.ColB?

I've tried inner joins, subqueries, left joins, but it just isn't clicking.

[tirna]

This is untested, but maybe try something like this:

 
select t1.colA, count(*) 
from table1 t1, table2 t2
where t1.colA = t2.colB
group by t1.colA

all my fingers and toes are crossed

[sigmahawk]

This is untested, but maybe try something like this:

 
select t1.colA, count(*) 
from table1 t1, table2 t2
where t1.colA = t2.colB
group by t1.colA

all my fingers and toes are crossed

I gave that a try, but it only returned results where the count was at least one. I need a 0 value returned for cases where there is a value in t1.colA that does not match any value in t2.colB

[sridhar_423]

try this

SELECT a.ColA, (SELECT COUNT(1) FROM table2 where ColB = a.ColA) from table1 a;

i hope table1.ColA and table2.ColB are indexed.