Jarla Jarla - 5 months ago 8
MySQL Question

How can I combine GROUP_CONCAT and LEFT JOIN?

This is my table animals:

╔══════════╦══════╗
║ animal ║ id ║
╠══════════╬══════╣
║ dog ║ 1 ║
║ cat ║ 4 ║
║ cat ║ 3 ║
║ bird ║ 1 ║
╚══════════╩══════╝


This is my table names:

╔═══════╦══════╗
║ id2 ║ name ║
╠═══════╬══════╣
║ 1 ║ alan ║
║ 2 ║ bob ║
║ 3 ║ john ║
║ 4 ║ sam ║
╚═══════╩══════╝


This is my expected result:

╔══════════╦═════════════╗
║ dog ║ alan ║
║ cat ║ sam,john ║
║ bird ║ alan ║
╚══════════╩═════════════╝


I tried this solution:

$sql = '
SELECT n.*,
x.grouped_name FROM names n
LEFT JOIN (SELECT a.id,
GROUP_CONCAT(a.animals) AS grouped_name FROM animals a GROUP BY a.id) x
ON x.id = n.id2';

foreach ($pdo->query($sql) as $row) {
echo '<td>'.$row['animal'].' </td>';
echo '<td>'.$row['grouped_name'].' </td>';
}


But I do not get a result.

I also tried this solution:

$sql = ' SELECT n.*, (SELECT GROUP_CONCAT(a.id) FROM animals a WHERE a.id = n.id2) AS grouped_name FROM names n';


But my result is this:

╔══════════╦═════════════╗
║ ║ 1,1 ║
║ ║ 3 ║
║ ║ 4 ║
╚══════════╩═════════════╝

Answer
SELECT t1.animal, GROUP_CONCAT(COALESCE(t2.name, "") separator ',') AS grouped_name
FROM animals t1
LEFT JOIN names t2
    ON t1.id = t2.id2
GROUP BY t1.animal

Have a look at the Fiddle to see the behavior when an animal does not match to anyone in the names table. To handle this case, I use COALESCE() on the name to replace a NULL value by an empty string before group concatenating.

SQLFiddle