CODEWITHSUNDEEP
mysql
Algoleigol
Algoleigol

Reputation: 201

MYSQL List all products with all categories

I have this schema:

table_products: product_id, name, price,

table_categories: category_id, name,

table_categories_products: category_id, product_id

I want list all products from my db including all the categories of each product.

Actually i use this query:

SELECT p.id, p.name, p.price, GROUP_CONCAT(c.category_id, ';', c.name SEPARATOR ',')
FROM table_products
LEFT JOIN table_categories_products tcp ON tcp.product_id=p.product_id
LEFT JOIN table_categories c ON c.category_id=p.product_id
GROUP BY p.id

The problem is that one product could be inside unlimited categories and the group_concat has a size limit.

Update

I had already considered the option of increase "group_concat_max_len", but dont't allow for an infinite string

Upvotes: 1

Views: 1026

Answers (1)

mjpolak
mjpolak

Reputation: 769

You can change group_concat max size by :

SET [GLOBAL | SESSION] group_concat_max_len = val;

default is 1024, and maximal settable value is 1073741824

at least that is what doc says: http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

Upvotes: 2

Related Questions