dwerty dwerty - 5 months ago 20
SQL Question

MySQL query to get all photos

I have two tables, they are properties and property_images. The fields in properties table are as below

properties

property_id
name
address
city
type


property_images

prop_img_id
photoname
property_id


I have a query to get the results from both the tables. But when I run the query I get only one image for each property. But I get the count of images right. I want to get all photos for a property in the query. The query I have is this:

SELECT *, photo, COUNT(property_photo.photo) as photocount
FROM properties LEFT JOIN
property_photo
ON properties.property_id = property_photo.property_id
GROUP BY properties.property_id
LIMIT 6

Answer

You can use GROUP_CONCAT():

SELECT p.*, COUNT(pp.photoname) as photocount,
       GROUP_CONCAT(pp.photoname SEPARATOR '|')
FROM properties p LEFT JOIN
     property_photo pp
     ON p.property_id = pp.property_id
GROUP BY p.property_id
LIMIT 6;

This puts the names of the photos in a string, with a vertical bar separating each name.