MySQL GROUP_CONCAT() groups all rows

Question

I have the following query:

select card.id as id, photo.image as photo
from card
left outer join card_image as photo on (photo.card=card.id)

which returns

+----+-------+
| id | photo |
+----+-------+
|  1 |     2 |
|  1 |     3 |
|  1 |     4 |
|  2 |     5 |
|  2 |     6 |
+----+-------+

If I change photo.image as photo to group_concat(photo.image) as photos I get the following result

+----+---------------+
| id | photo         |
+----+---------------+
|  1 |     2,3,4,5,6 |
+----+---------------+

But my expectation is

+----+-----------+
| id | photo     |
+----+-----------+
|  1 |     2,3,4 |
|  2 |     5,6   |
+----+-----------+

i.e. I want to get a group of photos for each card by id

Answer 1

GROUP_CONCAT() is an aggregate function, like MAX(). It produces one row for each group. Groups are defined by a GROUP BY clause, and in the absence of such a clause -- as in your case -- all rows belong to the same group. You apparently want to group by card.id:

select
  card.id as id,
  group_concat(photo.image) as photos
from
  card
  left outer join card_image as photo
    on (photo.card = card.id)
group by card.id

Note also that with an aggregate query, standard SQL permits selecting only the grouping columns and functions of the groups. In your original example there are no grouping columns, so you rely on a MySQL extension to be able to select card.id at all. The code I present above, on the other hand, is standard in that regard (but group_concat() is still a MySQL-ism).