Select an array per row?

Question

I have 2 tables:

1. products
- product_id
- title
2. product_categories
- product_id
- category_id

Each product can have more than one category. I store this information in the product_categories table. I want to be able to SELECT all the category_ids when I select a product in 1 query. How can I do this? I have the following so far, but it will only select 1 category id:

SELECT
  p.*,
  (SELECT
     category_id
   FROM 
     product_categories
   WHERE product_id = p.product_id) as category_ids
FROM
   products AS p

Answer 1

select products.*,product_categories from left outer join product_categories on product_categories.product_id = products.product_id

Answer 2

You could use a left join, Group By, and GROUP_CONCAT

http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql

SELECT products.*, GROUP_CONCAT(category_id SEPARATOR ' ')
FROM products LEFT JOIN product_categories
                ON product_categories.product_id = products.product_id
GROUP BY product_id;