SELECT using JOIN, SUBQUERY, UNION?
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:
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,
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
A small step.
The following query probably gives erroneous results:
but report the results if yoiu can.
select Bill.c1 as name,
count(Bill.c1) as Bill,
count(Mike.c1 as Mike
join Mike on Bill.c1=Mike.c1
group by name
The correst resukt
Here is a revised in working order query!
select Mike.c1 as name, count(Mike.c1) as MikeCount,
T1.Tcount as BillCount
(select Bill.c1 as Bills,
count(Bill.c1) as Tcount
group by Bill.c1) as T1
on Mike.c1 = T1.Bills
group by Mike.c1
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)