Spiff Posted January 2, 2008 Share Posted January 2, 2008 My simplified MySQL database contains two databases with data like this cities idname 1Amsterdam 2Brussels hotels idnamecity_id 1Hotel A11 2Hotel A21 3Hotel B12 In this case, there are 2 hotels in Amsterdam, and 1 in Brussels. This is the information I want to extract with a single query. I use: SELECT cities.name, COUNT(hotels.city_id) AS num FROM cities INNER JOIN hotels ON (cities.id = hotels.city_id) GROUP BY hotels.city_id ORDER BY cities.name I would like to have the result: cities.namenum Amsterdam2 Brussels1 But I get cities.namenum Amsterdam1 Amsterdam1 Brussels1 How do I adjust my query? Quote Link to comment https://forums.phpfreaks.com/topic/84184-using-count-and-join-in-a-single-query/ Share on other sites More sharing options...
remyaarun Posted January 2, 2008 Share Posted January 2, 2008 I tried out this same code with my tables. And its correctly working. Quote Link to comment https://forums.phpfreaks.com/topic/84184-using-count-and-join-in-a-single-query/#findComment-428673 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.