Sort one table via another

[bo0]

I have two tables in a database. Let’s call them “names” and “numbers”.

I want to display all the information from the “names” table in a list. I want the list to be ordered by the number of rows relevant to each name in the “numbers” table.

For example:

(id: 1) NameOne (45 rows for id: 1 in “numbers” table)
(id: 2) NameTwo (102 rows for id: 2 in “numbers” table)
(id: 3) NameThree
(id: 4) NameFour
[so id:2 would come first in the list!!]

I want the above list to be sorted by the number of rows in the “numbers” table that contain the relevant “names” id.

Any ideas on how I can go about doing this?

[Piranha]

Something like this should probably work:

$sql = "SELECT name.id, name.name, COUNT(number.nameid) as no
FROM name
LEFT JOIN number
   ON name.id = number.nameid
GROUP BY name.id
ORDER BY COUNT(number.nameid)"

If this is something that will be used very much I recommend you to add a column in name with the count. That count should be incremented by one each time something is added in the number table.

Edit: Changed to a left join, then the rows with 0 results will show as well.