MySQL left join, limit 1 row

Hello all.
this query works well.
But I came up with one problem, when I left join photos, I get as many rows as user has photos.

is there someway to do for example:
`order by photos.id DESC limit 1`
In the middle of left join or somehow else ?

My current SQL query :

SELECT `users`.`id` , `users`.`name` , `cities`.`name` AS `city` , `photos`.`photo` AS photo
FROM users
LEFT JOIN `cities` ON users.city = cities.id
LEFT JOIN `photos` ON users.id = photos.id
WHERE users.id
IN ( 77, 165, 166, 169, 164, 194, 195 )

Code (markup):

Thank you guys for your time!

cheers,
Jaroslav

 

Use GROUP BY to limit 1 picture per user

SELECT `users`.`id` , `users`.`name` , `cities`.`name` AS `city` , `photos`.`photo` AS photo
FROM users
LEFT JOIN `cities` ON users.city = cities.id
LEFT JOIN `photos` ON users.id = photos.id
WHERE users.id
IN ( 77, 165, 166, 169, 164, 194, 195 )
GROUP BY users.id

 

Hello mwasif.
I have just spoted that I accidenly did wrote a reply, but it didn't show on the list.

I wanted to thank you, since it works like it should

Thanks for you time, and sorry for a delay

Have a good time!

 

You are welcome.