How to prevent GROUP_CONCAT from creating a result when no input data is present?

mysql group-concat
Der Hochstapler picture Der Hochstapler · Sep 6, 2010 · Viewed 9.5k times · Source

Given the following MySQL query:

SELECT
  `show`.`id`
  , GROUP_CONCAT( `showClips`.`clipId` ORDER BY `position` ASC ) AS 'playlist'
FROM
  `show`
  INNER JOIN
    `showClips`
      ON
        ( `show`.`id` = `showClips`.`showId` )
;

I want to retrieve a list of all "shows" from the database, including the ids of contained "clips".

This works fine, as long as there are entries in the show table. For this problem, let's assume all tables are completely empty.

GROUP_CONCAT will return NULL and thus forcing a row into the result (which contains only NULL values).

My application will then think that one show/result exists. But that result will be invalid. This can of course be checked, but I feel like this could (and should) be prevented in the query already.

Answer

Daniel Vassallo picture Daniel Vassallo · Sep 6, 2010

You should simply add a GROUP BY at the end.

Test case:

CREATE TABLE `show` (id int);
CREATE TABLE `showClips` (clipId int, showId int, position int);

SELECT 
   `show`.`id`,
   GROUP_CONCAT( `showClips`.`clipId` ORDER BY `position` ASC ) AS 'playlist'
FROM  `show`
INNER JOIN `showClips` ON ( `show`.`id` = `showClips`.`showId` )
GROUP BY `show`.`id`;

Empty set (0.00 sec)

Related questions

Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like: SELECT hobbies FROM peoples_hobbies WHERE person_id = 5; My Output: shopping fishing coding but instead I just want 1 row, 1 col: Expected Output: shopping, fishing, coding The reason is that I'm selecting multiple values …

Read More
How to use GROUP_CONCAT in a CONCAT in MySQL

If I have a table with the following data in MySQL: id Name Value 1 A 4 1 A 5 1 B 8 2 C 9 how do I get it into the following format? id Column 1 A:4,5,B:8 2 C:9 I think I have to use GROUP_CONCAT. …

Read More
MySQL and GROUP_CONCAT() maximum length

I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters. I'm very well aware that I can change the param group_concat_…

Read More