concat the output of the subquery?

Question

i have a query which would return values but i need them as a single output separated by commas..

So i tried to concat the output with the comma but it didn't work?

select id from videos where duration=0;  /// this would return some rows

I tried concat and concat_ws but didn't work

select concat(select concat(id,',') from videos where duration=0);
select concat((select id from videos where duration=0),',');
select concat_ws(',',(select id from videos where duration=0));

i need the id's of all rows with the comma separtor

for example the output should be 1,4,6,78,565

any ideas?

Answer 1

This is what group_concat does.

select group_concat(id) as video_list
from videos 
where duration=0

Answer 2

Use group_concat :

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.

SELECT
  GROUP_CONCAT(id)
FROM
  videos
WHERE
  duration=0

Answer 3

Try to use Group_CONCAT

     GROUP_CONCAT([DISTINCT] expr [,expr ...]
         [ORDER BY {unsigned_integer | col_name | expr}
             [ASC | DESC] [,col_name ...]]
         [SEPARATOR str_val])

Refer http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat