GROUP_CONCAT Question

Question

So I am trying to do multiple GROUP_CONCAT()s in a query on the same column with if statements.
Even though the data is there, it is not giving me accurate info. I tried upping the group_concat_max_len, but I still get the same incorrect data.

Here is an example:

SELECT 
    field1,
    field2,
    GROUP_CONCAT(DISTINCT IF(field3 = 2, field4, NULL)) list1,
    GROUP_CONCAT(DISTINCT IF(field3 = 3, field4, NULL)) list2,
    GROUP_CONCAT(DISTINCT IF(field3 = 4, field4, NULL)) list3
FROM table1
GROUP BY field5

The results in the list1, list2 and list3 are not accurate, though the data is. Am I hitting some constraint from doing it this way?

Answer 1

SELECT field1,field2,last1,list2,list3 FROM
(
    SELECT
        field1, 
        field2,
        field5,
        GROUP_CONCAT(DISTINCT IF(field3 = 2, field4, 'NULL')) list1, 
        GROUP_CONCAT(DISTINCT IF(field3 = 3, field4, 'NULL')) list2, 
        GROUP_CONCAT(DISTINCT IF(field3 = 4, field4, 'NULL')) list3 
    FROM
        table1 
    GROUP BY
        field1,field2,field5
) A;

I changed the SELECT list, changed the GROUP BY, changed NULL to 'NULL', put the whole query in a subquery, and pull out of the subquery everything except field5. Please see if this produces the desired result.

If you want this query to run fast, please add this index:

ALTER TABLE table1 ADD INDEX (field1,field2,field5);