Q:

Get all users, with all their images

I have 2 mysql tables :

users table 
table that contains gallery images for each user 

My users table looks like :
id | name
---------
1   Ryan
2   James
3   Dave

My user_gallery_images tables looks like :
id | user_id | image
--------------------
1    1         image.jpg
2    1         image2.jpg
3    2         image3.jpg
4    2         image4.jpg

I was wondering if there was a query that would retrieve all users, and get all the images for that user.
The expected result should look like :
id | name | images
-------------------
1    Ryan   image.jpg,image2.jpg
2    James  image3.jpg,image4.jpg
3    Dave   

Thank you

A:

You will have to use a LEFT JOIN rather than an INNER JOIN because you want to retrieve David who doesn't have images. And you will need to use GROUP_CONCAT
SELECT u.id, u.name, GROUP_CONCAT(image) from users u 
LEFT JOIN user_gallery_images g 
ON u.id = g.user_id GROUP by u.id

Note this query will work on mysql 5.7 only if you have a PRIMARY KEY on users.id. Will work on mysql < 5.7 regardless of the primary key

