Join Mutiple Tables and Counting

[shooter0220]

Say I have a country table with country_id.

Also, state table with state_id and country_id.

and, city table with city_id, state_id, country_id.

Here is what I want to do:

I want to count the number of cities and states in each country.
For example,

SELECT country.name, COUNT(*)
FROM country
LEFT JOIN state USING (country_id)
GROUP BY country.name

This will count the number of states in each country. If I replace state with city, it will give the number of cities in each country.

What I want is to have the number of states and cities together:

name #of city # of state
country1 30 5
country2 200 40
country3 4000 50

Something like that.
Any ideas?
Thanks for your help.

[tha_mink]

$result = mysql_query("SELECT country.name, country.country_id, count(state.state_id) as state_count, count(city.city_id) as city_count FROM country LEFT JOIN state ON country.country.id=state.country_id LEFT JOIN city ON country.country_id=city.country_id");

$country = mysql_fetch_object($result);

Print"<p>$country->name $country->state_count $country->city_count</p>";