CODEWITHSUNDEEP
phpmysql
SimeriaIonut
SimeriaIonut

Reputation: 586

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 (cities_countries.id = timeline_elements.city_id)
WHERE cities_countries.name LIKE '%Sibiu%'

Upvotes: 1

Views: 36

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

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.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, ....

Upvotes: 1

Related Questions