uema uema - 5 months ago 16
MySQL Question

1 to many select and concat

I have two tables:

[customer]
id
name

[customer_photo]
id
customer_id
photo


I want to select all customers and their photos.

This query is doing it, but getting only users who have at least one photo:

SELECT customer.id, name, GROUP_CONCAT(cp.photo) as photos
FROM customer
JOIN customer_photo cp ON cp.customer_id = customer.id
GROUP BY customer.id


I want to get all users, even if they don't have a photo.

Answer

Just by adding the LEFT join

SELECT customer.id, name, GROUP_CONCAT(cp.photo) as photos 
FROM customer 
LEFT JOIN customer_photo cp ON cp.customer_id = customer.id 
GROUP BY customer.id
Comments