I have a two tables, one with cities (id and city name) and one with pictures of the cities (city_id, etc).
Let's say I'm looking for the city called Sibiu. That should return 3 results, since there are more cities like that in the table (Miercurea Sibiului, Sibiu, Poiana Sibiului), but it only returns one.
Also, as a note, the timeline_elements doesn't have any pictures of the city yet.
SELECT cities_countries.*, COUNT(timeline_elements.city_id) as number_of_photos
LEFT JOIN timeline_elements on (cities_countries.id = timeline_elements.city_id)
WHERE cities_countries.name LIKE '%Sibiu%'
GROUP BY and also explicitly mention all the column names for the
cities_countries table. I consider these are the columns in the
id, city_id, city_name.
Also set alias name for each table for the better readability.
SELECT C.id, C.city_id, C.city_name, .... , COUNT(T.city_id) as number_of_photos FROM cities_countries C LEFT JOIN timeline_elements T ON C.id = T.city_id WHERE C.name LIKE '%Sibiu%' GROUP BY C.id, C.city_id, C.city_name, ....