SELECT using JOIN, SUBQUERY, UNION?

[willie_wonka]

Hi all,

I have two identical tables each with a single column (id mediumint)
Table "Bill" has 12 rows: values (A,B,B,C,C,C,C,C,D,D,E,E)
Table "Mike" has 5 rows : value (A,C,C,D,F)

I want to query these tables and produce a result like:

---Bill----Mike
A---1-------1
B---2-------0
C---5-------2
D---2-------1
E---2-------0
F---0-------1

This tells me that there are 2 Bs in Bill, and 5 Cs in Bill, and 0 Es in Mike, etc., I have been trying to do a join, but I think I need something else?

Use count, group by...hmmm

Thanks in advance for your help,
Willie

[willie_wonka]

So, nearly 100 pair of eyes, not one comment...hmmm Just tell me its to simple or to difficult! Or at least, point me in some direction... Many thanks, WW

[WyCnet]

The following query probably gives erroneous results:

select Bill.c1 as name,
count(Bill.c1) as Bill,
count(Mike.c1 as Mike
from Bill
join Mike on Bill.c1=Mike.c1
group by name

but report the results if yoiu can.

[WyCnet]

Here is a revised in working order query!

select Mike.c1 as name, count(Mike.c1) as MikeCount,
T1.Tcount as BillCount
from Mike
join
(select Bill.c1 as Bills,
count(Bill.c1) as Tcount
from Bill
group by Bill.c1) as T1
on Mike.c1 = T1.Bills
group by Mike.c1

Enjoy.