MySQL One to Many order by number found in joined table

Question

Two tables:

table_a
-------
table_a_id: (primary, int)

table_b
-------
table_a_id: (index, int, from table_a)
table_b_value: (varchar)

A one to many relationship between table_a_id and table_b_value.

Given a query like this:

SELECT DISTINCT(table_a_id) FROM table_a 
JOIN table_b ON table_a.table_a_id=table_b.table_a_id

I want to order by the number of occurrences of table_a_id in table_b. I'm not really sure how to write this in MySQL.

Answer 1

SELECT COUNT(table_b.table_a_id) AS count 
FROM table_a 
JOIN table_b ON table_b.table_a_id = table_a.id 
GROUP BY table_b.table_a_id 
ORDER_BY count DESC 

Answer 2

Why do you need to join the tables at all in this case?

SELECT table_a_id FROM table_b GROUP BY table_a_id order by count(table_a_id) desc

Answer 3

Hey. Try something like:

SELECT 
        a.table_a_id,
        COUNT(b.table_a_id) AS `a_count`
FROM    table_a AS a
LEFT JOIN   table_b AS b
    ON  a.table_a_id = b.table_a_id
GROUP BY a.table_a_id
ORDER BY `a_count`

Answer 4

Using your example

SELECT table_a_id, COUNT(*)
FROM table_b
GROUP BY table_a_id

A real world example

If you want a table like:

post_id  comments_count
1        20
2        125
3        43

If you might need other information from the A table, you can:

select p.id, count(c.*) comments_count
from posts p
join comments c on c.post_id = p.id
group by p.id

If you don't need anything from the A table, you can simplify:

select post_id, count(*)
from comments
group by post_id