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

What do you want the output to look like? An example would help. — Ike Walker, Mar 05 '10 at 14:52
like a multi-dim array for each row. So all the general product information could be retrieve in PHP like $row['title'] but the categories would be a further array $row['product_categories'][0] would get the first product category id. — David, Mar 05 '10 at 14:55

score 0 · Answer 1 · answered Mar 05 '10 at 14:12

0

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

answered Mar 05 '10 at 14:12

Axarydax

16,353
21
92
151

score 0 · Accepted Answer · edited May 23 '17 at 12:07

0

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

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;

edited May 23 '17 at 12:07

Community

1
1

answered Mar 05 '10 at 14:20

SorcyCat

1,206
10
19

Very close to what I need, however I need it to return it as an Array. I will be using it with sphinx's MVA datatype. – David Mar 05 '10 at 14:32

Select an array per row?

2 Answers2