GROUP CONCAT not working in a subquery - mysql

Question

I have a mysql query which I've written in order to grab a group of attributes in the database based on IDs, which relate to a specific group. I'm using OpenCart 2 for my site, and my subquery is as follows:

SELECT GROUP_CONCAT(attribute_id) AS attr_ids 
FROM oc79_attribute 
WHERE attribute_group_id = 13

Which returns:

44,45,46,47

When I write this within the query which I need to get the attribute names, I only get one result:

SELECT * 
FROM oc79_attribute_description 
WHERE attribute_id IN(SELECT GROUP_CONCAT(attribute_id) AS attr_ids 
                      FROM oc79_attribute WHERE attribute_group_id = 13)

I only get the result from attribute_id 44, and not the others even though I know the records exist.

Is this the right way to approach this, or am I just missing something?

EDIT:

To clarify, if I write:

SELECT * FROM oc79_attribute_description WHERE attribute_id IN(44,45,46,47)

I get the correct result of 4 records.

Thanks in advance

See http://stackoverflow.com/a/11957706/575376 – juergen d Nov 11 '15 at 16:18 — juergen d, Nov 11 '15 at 16:18

score 3 · Accepted Answer · answered Nov 11 '15 at 16:18

There is no need for GROUP_CONCAT:

SELECT * 
FROM oc79_attribute_description 
WHERE attribute_id IN(SELECT attribute_id AS attr_ids 
                      FROM oc79_attribute
                      WHERE attribute_group_id = 13);

See the difference between:

WHERE attribute_id IN ('44,45,46,47')     -- one string

and

WHERE attribute_id IN ('44','45','46','47')  -- multiple values

GROUP_CONCAT will return one string with , as separator, what you need is multiple values.

score 1 · Answer 2 · answered Nov 11 '15 at 16:48

If the attribute table only has one row per attribute_id then you can use a join simple:-

SELECT oc79_attribute_description.* 
FROM oc79_attribute_description 
INNER JOIN oc79_attribute 
ON oc79_attribute_description.attribute_id = oc79_attribute.attribute_id 
AND oc79_attribute.attribute_group_id = 13

If it has multiples then you could use DISTINCT:-

SELECT DISTINCT oc79_attribute_description.* 
FROM oc79_attribute_description 
INNER JOIN oc79_attribute 
ON oc79_attribute_description.attribute_id = oc79_attribute.attribute_id 
AND oc79_attribute.attribute_group_id = 13

If you really wanted to search through a string of comma separated values (and I really would advise against it) then you could use FIND_IN_SET:-

SELECT * 
FROM oc79_attribute_description 
WHERE FIND_IN_SET(attribute_id, (SELECT GROUP_CONCAT(attribute_id) AS attr_ids 
                      FROM oc79_attribute WHERE attribute_group_id = 13))

GROUP CONCAT not working in a subquery - mysql

2 Answers2