0

I have these 3 tables which put products and their categories in a N:N relationship:

products:
product_id | product_name
1          | Apples
2          | Shrimps
3          | Water

categories:
cat_id   | cat_name
1        | Vegetable
2        | Seafood
3        | Fresh
4        | Drinks
5        | Non-alcohol

products_to_categories:
product_id | cat_id
2          | 2
2          | 3
3          | 3
3          | 4
3          | 5
...

I would like to SELECT CONCAT... to have rows like this:

product_id | product_name | product_categories
2            Shrimps        Seafood, Fresh
3            Water          Fresh, Drinks, Non-alcohol

Is there a way to do this in MySQL?

Chef Tony
  • 435
  • 7
  • 14
  • 1
    I think that your relation is `N:N` and not `1:N` – Marcos Echagüe Dec 17 '19 at 15:24
  • 1
    Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). – Strawberry Dec 17 '19 at 15:26
  • 1
    I think that it is that youre looking : ```select p.product_id, p.product_name , CONCAT(p.product_name, ', ', c.cat_name) from products_to_categories pc JOIN products p on pc.product_id = p.product_id JOIN categories c on c.cat_id = pc.cat_id``` – Marcos Echagüe Dec 17 '19 at 15:46
  • @MarcosEchagüe fixed :) ty – Chef Tony Dec 17 '19 at 17:14

0 Answers0