Achilleterzo Achilleterzo - 18 days ago 5
MySQL Question

Mysql group_concat(id) as ids in a left join and using ids to select all columns in id group

i have a table that contains some articles with it's own ID and shared SKU key.
I've tried to make the query with a left join and using group result to take all ids returned from the query.

My data structure is like that:

id - name - sku - ...
1 - felix - cat
2 - tom - cat - ...
3 - sylvester - cat - ...
4 - red - pen - ...
5 - blue - pen - ...


I tried to use this query:

SELECT * FROM `test`
[LEFT/RIGHT/INNER] JOIN
(
SELECT GROUP_CONCAT(DISTINCT id) AS idsgroup FROM `test` WHERE (attribute_name = 'sku') GROUP BY value_name LIMIT 0, 3
) bind
ON id IN (bind.idsgroup);


this query is wrong, it return only 1 id per group instead all ids selected from concat or in LEFT JOIN case, obviously all rows.

Any suggestion workaround to achieve the right result?

EDIT:

here a fiddle with the structure:
http://sqlfiddle.com/#!9/b6747a

And the query i tried into:

SELECT * FROM `view_test`
INNER JOIN
(
SELECT GROUP_CONCAT(DISTINCT entity_id) AS idsgroup FROM `view_test` WHERE (attribute_name = 'sku') GROUP BY value_name LIMIT 0, 3
) bind
ON entity_id IN (bind.idsgroup);


As this pic show, my result lost some ids, part of the group.
query result

EDIT 2:
after i used FIND_IN_SET() suggested by Kickstart the result is the expected:

SELECT * FROM `view_test`
INNER JOIN
(
SELECT GROUP_CONCAT(DISTINCT entity_id) AS idsgroup FROM `view_test` WHERE (attribute_name = 'sku') GROUP BY value_name LIMIT 0, 3
) bind
ON FIND_IN_SET(entity_id, bind.idsgroup);


Right result

Answer

The simple fix would appear to be to use FIN_IN_SET for the join. But this is a bit of a hack and will not be that quick.

SELECT * 
FROM `view_test` 
INNER JOIN 
(
    SELECT GROUP_CONCAT(DISTINCT entity_id) AS idsgroup 
    FROM `view_test` 
    WHERE (attribute_name = 'sku') 
    GROUP BY value_name 
    LIMIT 0, 3 
) bind
ON FIND_IN_SET(entity_id, bind.idsgroup);

Further not sure why you have a LIMIT on the sub query, especially without an order clause.

Possibly better to use a sub query to just get the DISTINCT entity id with an attribute_name of sku and join against that

SELECT * 
FROM `view_test` 
INNER JOIN 
(
    SELECT DISTINCT entity_id 
    FROM `view_test` 
    WHERE (attribute_name = 'sku') 
) bind
ON view_test.entity_id = bind.entity_id
Comments