samedi 11 juin 2016

MySQL: Error Code 1111. Invalid use of group function. (GROUP_CONCAT within GROUP_CONCAT)

I'm receiving an "Invalid use of group function" error when executing this SELECT statement.

SELECT kits.id, kits.is_quote,
GROUP_CONCAT(
CONCAT_WS('|||', kits_table.id, kits_table.is_quote,
    GROUP_CONCAT(
        CONCAT_WS('|', parts_table.id, parts_table.is_quote) 
    SEPARATOR '||'),
    GROUP_CONCAT(
        CONCAT_WS('|', labor_table.id, labor_table.is_quote) 
    SEPARATOR '||')
)
SEPARATOR '||||') as kits,
GROUP_CONCAT(CONCAT_WS('|', parts.id, parts.is_quote) SEPARATOR '|||') as parts,
GROUP_CONCAT(CONCAT_WS('|', labor.id, labor.is_quote) SEPARATOR '|||') as labor
FROM kits
LEFT  JOIN kits as kits_table ON kits_table.kit_id = kits.id
LEFT OUTER JOIN parts as parts_table ON parts_table.kit_id = kits_table.id
LEFT OUTER JOIN labor as labor_table ON labor_table.kit_id = kits_table.id
LEFT OUTER JOIN parts ON parts.kit_id = kits.id
LEFT OUTER JOIN labor ON labor.kit_id = kits.id
WHERE kits.id = '1'
GROUP BY kits.id;

I need to be able to SELECT a kit from a database, and within that kit I need the query to return other kits, parts, and labor, with the kits part of that equation also returning parts and labor. If I remove this GROUP_CONCAT(*) as kits statement then the query works fine.

Aucun commentaire:

Enregistrer un commentaire