SimeriaIonut SimeriaIonut - 1 year ago 82
PHP Question

SQL left join only returns one row instead of many

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
FROM cities_countries
LEFT JOIN timeline_elements on ( = timeline_elements.city_id)
WHERE LIKE '%Sibiu%'

Answer Source

Add the GROUP BY and also explicitly mention all the column names for the cities_countries table. I consider these are the columns in the cities_countries table. id, city_id, city_name.

Also set alias name for each table for the better readability.

SELECT, C.city_id, C.city_name, ....
       , COUNT(T.city_id) as number_of_photos 
FROM cities_countries C  
LEFT JOIN timeline_elements T ON = T.city_id
WHERE LIKE '%Sibiu%'
GROUP BY, C.city_id, C.city_name, ....
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download