help with database query - PLEASE

[nnichols]

user TABLE
USER_ID, USERNAME, REGION_ID, COUNTRY_ID

region TABLE
REGION_ID, REGION_NAME, COUNTRY_ID

query -
SELECT REGION_NAME, REGION_ID, COUNT(*) AS num_users
FROM region, user
WHERE region.COUNTRY_ID='$country'
AND user.REGION_ID=region.REGION_ID
GROUP BY region.REGION_ID
ORDER BY region.REGION_NAME;

The purpose here is to display a list of "regions" for the chosen "country", along with the number of users in each region. This query only returns the regions for which there are users, but I would like to display regions with no users as well. Can someone please suggest how I might do this. My SQL isn't up to much.

Thanks in advance
Nick

[Anon]

You need to use a LEFT JOIN instead of an INNER JOIN. I dodn't test this but it should be close:

SELECT REGION_NAME, REGION_ID, COUNT(*) AS num_users
FROM region
LEFT JOIN user
ON user.REGION_ID=region.REGION_ID
WHERE region.COUNTRY_ID='$country'
GROUP BY region.REGION_ID
ORDER BY region.REGION_NAME;

[nnichols]

thanks tomhath

[nnichols]

the only problem with the LEFT JOIN is that it then shows a count of 1 for regions with no users and for regions with only one user. Is there any way around this.

Thanks for your help
Nick