Help with an SQL statement

I have three tables. Let's call them a, b, and a_to_b. a_to_b contains (among other things) the unique ids from both a and b to provide linkage between a and b (obviously). a looks something like this:

a_id, max_b, ...

a_to_b looks something like:

a_id, b_id, ...

I would like to create a query that is something like:

select a.a_name, a.max_b, a_to_b.(number of times a_id appears in a_to_b) for a, a_to_b where ...

only I don't know the right questions to ask google to find out how to do it.

To summarize, I don't care about the the individual occurrences of b_id in a_to_b, just how many times a_id appears in that table.

I hope that made enough sense to be able to see what I'm trying to accomplish. Thanks for the help.

EDIT

Now that I run it I realize that I am missing part of the query.

This is the full story. Sorry for the previous misdirection.

table A:

group_id
other_stuff

table B:

user_id
other_stuff

table A_to_B:

group_id
user_id
m_type (int used to determine user's role in the group)

Desired output:

a.group_id, a_to_b.count(of group_id where m_type=4), b.user_id (user_id from a_to_b from the group_id that was grouped where m_type=2)

Again, I apologize for the repost. I should have been more careful when asking the first time. I wish I were familiar enough with this stuff to be able to translate what Rasika gave but I tried a bunch of stuff and mysql just keeps yelling at me.

MORE EDIT

I did some more experimenting on what Rasika wrote and came up with this mess:

select classes.class_id, classes.spaces - ifnull(dUtC.students,0) as openings, classes.semester_id, dUtC.teacher, users.fname, users.lname from (select teachUtC.class_id as开发者_运维技巧 class_id, numUtC.students as students, teachUtC.user_id as teacher from (select class_id, count(*) as students from users_to_classes where participation_level=4 group by class_id) as numUtC right join (select class_id, user_id from users_to_classes where participation_level=2) as teachUtC on teachUtC.class_id=numUtC.class_id) as dUtC, classes, users where users.user_id=dUtC.teacher and dUtC.class_id=classes.class_id

It works but I can't help but think that I am doing it wrong... I'd like to know if there is a cleaner way to write that.

Thanks

You could use a group by with a count to generate this.

select a.a_name, a.max_b, count(*) as num_a_id from a join a_to_b on a.a_id=a_to_b.a_id group by a_id