Concatenating Subqueries with Multiple Results in MySQL

ERROR: Subquery returns more than 1 row
Error Code: 1242

The "Subquery returns more than 1 row" error may indicate that your data is not normalized properly especially if your data relationships are supposed to be one-to-one. However, if you have data with one-to-many relationships like items in multiple categories or parents with many children, then this error indicates that you need to group and aggregate your results.

GROUP_CONCAT (available since MySQL 4.1) is one of MySQL’s GROUP BY (Aggregate) Functions and makes subqueries with multiple results easy and manageable.

Example:

SELECT
	`element`.`id`,
	`element`.`name`,
	(SELECT
			GROUP_CONCAT(`element_category`.`name`)
		FROM
			`element_category`
		WHERE
			`element_category`.`element_id` = `element`.`id`
		GROUP BY
			`element_category`.`element_id`
	) as categories
FROM
	`element`;

Would return something like:

+----+---------------+---------------------+	
| id | name          | categories          |
+----+---------------+---------------------+
| 1  | brownies      | desserts,snacks     |
| 2  | granola bars  | breakfast,snacks    |
| 3  | potato chips  | side dishes,snacks  |
| 4  | fried chicken | main courses        |
+----+---------------+---------------------+

Of course, you could achieve the same results with this query:

select 
	`element`.`id`,
	`element`.`name`, 
	GROUP_CONCAT(`element_category`.`name`) as categories 
from 
	`element`, 
	`element_category` 
where 
	`element_category`.`element_id` = `element`.`id` 
group by 
	`element`.`id`;

These are simple examples, but imagine the power of group concatenating when your query gets complex with several joins and/or subqueries.