MySQL: Nested GROUP_CONCAT
<p>I'm receiving an "Invalid use of group function" error when executing this SELECT statement.</p>

<pre><code>SELECT kits.id, kits.is_quote,
GROUP_CONCAT(
CONCAT_WS('|||', kits_table.id, kits_table.name,
    GROUP_CONCAT(
        CONCAT_WS('|', parts_table.id, parts_table.name) 
    SEPARATOR '||'),
    GROUP_CONCAT(
        CONCAT_WS('|', labor_table.id, labor_table.description) 
    SEPARATOR '||')
)
SEPARATOR '||||') as kits,
GROUP_CONCAT(CONCAT_WS('|', parts.id, parts.name) SEPARATOR '|||') as parts,
GROUP_CONCAT(CONCAT_WS('|', labor.id, labor.description) 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;
</code></pre>

<p>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 <code>GROUP_CONCAT(*) as kits</code> statement then the query works fine. </p>

<p>Upon request of the tables I'm using these are the tables with the primary info you need: </p>

<p>Table Creation:</p>

<pre><code>CREATE TABLE `kits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kit_id` int(11) DEFAULT NULL,
  `is_quote` tinyint(4) NOT NULL DEFAULT '0',
  `name` varchar(45) DEFAULT NULL,
  `description` varchar(150) DEFAULT NULL,
  `quantity` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `KIT` (`kit_id`)
)

CREATE TABLE `labor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kit_id` int(11) DEFAULT NULL,
  `is_quote` tinyint(4) NOT NULL DEFAULT '0',
  `description` varchar(150) NOT NULL,
  `hours` varchar(45) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `KIT` (`kit_id`)
)

CREATE TABLE `parts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kit_id` int(11) DEFAULT NULL,
  `is_quote` tinyint(4) NOT NULL DEFAULT '0',
  `name` varchar(45) DEFAULT NULL,
  `description` varchar(150) DEFAULT NULL,
  `sale_price` varchar(45) DEFAULT '0.00',
  `quantity` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `KIT` (`kit_id`)
)
</code></pre>

<p>And insert statements:</p>

<pre><code>INSERT INTO `kits`
(`id`,
`kit_id`,
`is_quote`,
`name`,
`description`,
`quantity`)
VALUES
(1,0,0,"Main Kit", "Sample Description",1);

INSERT INTO `kits`
(`id`,
`kit_id`,
`is_quote`,
`name`,
`description`,
`quantity`)
VALUES
(2,1,0,"Kit within kit", "Sample Description",1);

INSERT INTO `parts`
(`kit_id`,
`is_quote`,
`name`,
`description`,
`sale_price`,
`quantity`)
VALUES
(1,0,"First Kit Part 1", "Part description","23.5",1);
 INSERT INTO `parts`
(`kit_id`,
`is_quote`,
`name`,
`description`,
`sale_price`,
`quantity`)
VALUES
(1,0,"First Kit Part 2", "Part description","23.5",1);

INSERT INTO `parts`
(`kit_id`,
`is_quote`,
`name`,
`description`,
`sale_price`,
`quantity`)
VALUES
(2,0,"Kit within kit part 1", "Sample Part Description","23.5",1);

INSERT INTO `parts`
(`kit_id`,
`is_quote`,
`name`,
`description`,
`sale_price`,
`quantity`)
VALUES
(2,0,"Kit within kit part 2", "Sample Part Description","23.5",1);

INSERT INTO `labor`
(`kit_id`,
`is_quote`,
`description`,
`hours`)
VALUES
(1,0,"First Kit labor 1","1.5");

INSERT INTO `labor`
(`kit_id`,
`is_quote`,
`description`,
`hours`)
VALUES
(1,0,"First Kit labor 2","1.5");

INSERT INTO `labor`
(`kit_id`,
`is_quote`,
`description`,
`hours`)
VALUES
(2,0,"Kit within kit labor 1","1.5");

INSERT INTO `labor`
(`kit_id`,
`is_quote`,
`description`,
`hours`)
VALUES
(2,0,"Kit within kit labor 2","1.5");

//Second Kit within kit.

INSERT INTO `kits`
(`id`,
`kit_id`,
`is_quote`,
`name`,
`description`,
`quantity`)
VALUES
(3,1,0,"Kit within kit 2", "Sample Description",1);

INSERT INTO `parts`
(`kit_id`,
`is_quote`,
`name`,
`description`,
`sale_price`,
`quantity`)
VALUES
(3,0,"Kit within kit part 1", "Sample Part Description","23.5",1);

INSERT INTO `parts`
(`kit_id`,
`is_quote`,
`name`,
`description`,
`sale_price`,
`quantity`)
VALUES
(3,0,"Kit within kit part 2", "Sample Part Description","23.5",1);

INSERT INTO `labor`
(`kit_id`,
`is_quote`,
`description`,
`hours`)
VALUES
(3,0,"Kit within kit labor 1","1.5");

INSERT INTO `labor`
(`kit_id`,
`is_quote`,
`description`,
`hours`)
VALUES
(3,0,"Kit within kit labor 2","1.5");
</code></pre>

<p>Here is sample output using the above INSERT values. Also note that there CAN be multiple kits within the kits key separated by ||||.</p>

<pre><code>+----+----------+----------------------------------------------------------------------------------------------------------------------------+------------------+-------------------+
| id | is_quote |                                                            kits                                                            |      parts       |       labor       |
+----+----------+----------------------------------------------------------------------------------------------------------------------------+------------------+-------------------+
|  1 |        0 | 2|||Kit within kit|||2|Kit within kit part 1||3|Kit within kit part 2|||2|Kit within kit labor 1||3|Kit within kit labor 2 | 1|First Kit Part | 1|First Kit labor |
+----+----------+----------------------------------------------------------------------------------------------------------------------------+------------------+-------------------+
</code></pre>