CODEWITHSUNDEEP
mysqlconcatenation
forrestmid
forrestmid

Reputation: 1504

MySQL: Nested 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.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;

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.

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

Table Creation:

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`)
)

And insert statements:

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");

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

+----+----------+----------------------------------------------------------------------------------------------------------------------------+------------------+-------------------+
| 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 |
+----+----------+----------------------------------------------------------------------------------------------------------------------------+------------------+-------------------+

Upvotes: 3

Views: 4893

Answers (4)

wchiquito
wchiquito

Reputation: 16551

Try:

mysql> SELECT
    ->   GROUP_CONCAT(
    ->     CONCAT_WS('|||', 0, 1, 
    ->               GROUP_CONCAT(CONCAT_WS('|', 2, 3) SEPARATOR '||')
    ->              )
    ->             ) `test`;
ERROR 1111 (HY000): Invalid use of group function

mysql> SELECT
    ->   GROUP_CONCAT(
    ->     CONCAT_WS('|||', 0, 1, 
    ->               (SELECT GROUP_CONCAT(CONCAT_WS('|', 2, 3) SEPARATOR '||'))
    ->              )
    ->             ) `test`;
+-------------+
| test        |
+-------------+
| 0|||1|||2|3 |
+-------------+
1 row in set (0,00 sec)

UPDATE

One possible option:

mysql> DROP TABLE IF EXISTS `parts`, `labor`, `kits`;
Query OK, 0 rows affected (0.00 sec)

mysql> 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`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> 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`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> 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`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `kits`
    -> (`id`,
    -> `kit_id`,
    -> `is_quote`,
    -> `name`,
    -> `description`,
    -> `quantity`)
    -> VALUES
    -> (1,0,0,"Main Kit", "Sample Description",1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `kits`
    -> (`id`,
    -> `kit_id`,
    -> `is_quote`,
    -> `name`,
    -> `description`,
    -> `quantity`)
    -> VALUES
    -> (2,1,0,"Kit within kit", "Sample Description",1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `parts`
    -> (`kit_id`,
    -> `is_quote`,
    -> `name`,
    -> `description`,
    -> `sale_price`,
    -> `quantity`)
    -> VALUES
    -> (1,0,"First Kit Part", "Part description","23.5",1);
Query OK, 1 row affected (0.00 sec)

mysql> 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);
Query OK, 1 row affected (0.00 sec)

mysql> 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);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `labor`
    -> (`kit_id`,
    -> `is_quote`,
    -> `description`,
    -> `hours`)
    -> VALUES
    -> (1,0,"First Kit labor","1.5");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `labor`
    -> (`kit_id`,
    -> `is_quote`,
    -> `description`,
    -> `hours`)
    -> VALUES
    -> (2,0,"Kit within kit labor 1","1.5");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `labor`
    -> (`kit_id`,
    -> `is_quote`,
    -> `description`,
    -> `hours`)
    -> VALUES
    -> (2,0,"Kit within kit labor 2","1.5");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT kits.id, kits.is_quote,
    ->     GROUP_CONCAT(
    ->         CONCAT_WS('|||', kits_table.id, kits_table.name,
    ->             (SELECT GROUP_CONCAT(
    ->                 CONCAT_WS('|', parts.id, parts.name) 
    ->             SEPARATOR '||') FROM parts WHERE parts.kit_id = kits_table.id),
    ->             (SELECT GROUP_CONCAT(
    ->                 CONCAT_WS('|', labor.id, labor.description) 
    ->             SEPARATOR '||') FROM labor WHERE labor.kit_id = kits_table.id)
    ->         )
    ->     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 ON parts.kit_id = kits.id
    ->     LEFT OUTER JOIN labor ON labor.kit_id = kits.id
    -> WHERE kits.id = 1
    -> GROUP BY kits.id\G
*************************** 1. row ***************************
      id: 1
is_quote: 0
    kits: 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
   parts: 1|First Kit Part
   labor: 1|First Kit labor
1 row in set (0.00 sec)

UPDATE 2

mysql> SELECT kits.id, kits.is_quote,
    ->   GROUP_CONCAT(DISTINCT
    ->       CONCAT_WS('|||', kits_table.id, kits_table.name,
    ->           (SELECT GROUP_CONCAT(DISTINCT
    ->               CONCAT_WS('|', parts.id, parts.name) 
    ->           SEPARATOR '||') FROM parts WHERE parts.kit_id = kits_table.id),
    ->           (SELECT GROUP_CONCAT(DISTINCT
    ->               CONCAT_WS('|', labor.id, labor.description) 
    ->           SEPARATOR '||') FROM labor WHERE labor.kit_id = kits_table.id)
    ->       )
    ->   SEPARATOR '||||'
    ->   ) as kits,
    ->   GROUP_CONCAT(DISTINCT CONCAT_WS('|', parts.id, parts.name) SEPARATOR '|||') as parts,
    ->   GROUP_CONCAT(DISTINCT 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 ON parts.kit_id = kits.id
    ->   LEFT OUTER JOIN labor ON labor.kit_id = kits.id
    -> WHERE kits.id = 1
    -> GROUP BY kits.id\G
*************************** 1. row ***************************
      id: 1
is_quote: 0
    kits: 2|||Kit within kit|||3|Kit within kit part 1||4|Kit within kit part 2|||3|Kit within kit labor 1||4|Kit within kit labor 2
   parts: 1|First Kit Part 1|||2|First Kit Part 2
   labor: 1|First Kit labor 1|||2|First Kit labor 2
1 row in set (0,00 sec)

Upvotes: 4

forrestmid
forrestmid

Reputation: 1504

This is a potential resolution to the problem, so it might help someone else. This specific query can be done in 2 SELECT statements, but the question is to have it done in 1 SELECT statement, so I won't mark this as an answer.

It's definitely not good if one wants to get more than one main kit, because you'd have to make one request for the list of main kits, and then iterate through a for-loop of those main kits making a query for every kit to get kits within the main kit. Very inefficient.

When the required kit id = 1, then:

//Fetch the main kit.
SELECT kits.id, kits.is_quote,
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 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;

//Fetch an array of kits where kit_id = 1
SELECT kits.id, kits.is_quote,
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 OUTER JOIN parts ON parts.kit_id = kits.id
LEFT OUTER JOIN labor ON labor.kit_id = kits.id
WHERE kits.kit_id = '1'
GROUP BY kits.id;

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

That's quite a strange naming policy - to have both a column called id AND a column called kit_id on a table called kits. So, I'm not sure if I've got this right. So never mind if it's what you think you're looking for or not; is the following a valid query...

SELECT k.id
     , k.name kit_name
     , k.description kit_description
     , k.quantity kit_quantity
     , p.name part_name
     , p.description part_description
     , p.sale_price part_price
     , p.quantity part_quantity
     , l.description labor_description
     , l.hours labor_hours 
  FROM kits k 
  LEFT 
  JOIN parts p 
    ON p.kit_id = k.id 
  LEFT 
  JOIN labor l 
    ON l.kit_id = k.id;

+----+----------------+--------------------+--------------+---------------------+-------------------------+------------+---------------+----------------------+-------------+
| id | kit_name       | kit_description    | kit_quantity | part_name           | part_description        | part_price | part_quantity | labor_description    | labor_hours |
+----+----------------+--------------------+--------------+---------------------+-------------------------+------------+---------------+----------------------+-------------+
|  1 | Main Kit       | Sample Description | 1            | First Kit Part      | Part description        | 23.5       | 1             | First Kit labor      | 1.5         |
|  2 | Kit within kit | Sample Description | 1            | Kit within kit part | Sample Part Description | 23.5       | 1             | Kit within kit labor | 1.5         |
+----+----------------+--------------------+--------------+---------------------+-------------------------+------------+---------------+----------------------+-------------+
2 rows in set (0.00 sec)

??

Upvotes: 0

giuseppe straziota
giuseppe straziota

Reputation: 443

I think the problem is the differents between select fields and group by fields.

this is the syntax for the GROUP BY clause in MySQL 

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

maybe you have to add only kits.is_quote to the group by fields, if the concat_group are seen as aggregate functions

Upvotes: 0

Related Questions