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? 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. 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
Archived
This topic is now archived and is closed to further replies.