How do I GROUP BY and then LEFT JOIN?

Question

I have this table:

------------------
| id   | foods   |
------------------
| 1    | cookies |
| 2    | cake    |
| 3    | milk    |
------------------

And this table:

------------------
| id   | colors  |
------------------
| 1    | red     |
| 1    | blue    |
| 1    | pink    |
| 2    | orange  |
| 2    | yellow  |
| 2    | purple  |
| 3    | cyan    |
| 3    | gold    |
| 3    | silver  |
------------------

I want to SELECT the foods from the first table and match them with the colors from the second table, based on id.

So I want the output to look like this:

--------------------------------------------
| id   | foods   | colors                  |
--------------------------------------------
| 1    | cookies | red, blue, pink         |
| 2    | cake    | orange, yellow, purple  |
| 3    | milk    | cyan, gold, silver      |
--------------------------------------------

How can I do this?

EDIT: This is not a duplicate. I am not trying to concatenate. I am trying to merge a concatenated table into a regular table based on ID.

Answer 1

Should look something like this:

SELECT f.food, GROUP_CONCAT(c.color SEPARATOR ' ') FROM food_table f join color_table c on f.food_id = c.id GROUP BY food;

Documentation can be found here:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat