5

I'm probably having a no-brain moment.

I want to return a series of numbers using GROUP_CONCAT from two fields in my database. I have done this so far using the following:

SELECT t_id,
CONCAT(GROUP_CONCAT(DISTINCT s_id),',',IFNULL(GROUP_CONCAT(DISTINCT i_id),'')) AS all_ids
FROM mytable GROUP BY t_id

This works fine but if i_id is NULL then of course I get an unnecessary comma. Is there a better way to do this so I don't end up with a comma at the end if i_id is NULL?

Pandy Legend
  • 1,102
  • 3
  • 15
  • 29

1 Answers1

14

You need to use CONCAT_WS to avoid extra comma for NULL values, try this:

SELECT t_id,
       CONCAT_WS(',', GROUP_CONCAT(DISTINCT s_id),
                 GROUP_CONCAT(DISTINCT i_id)) AS all_ids
FROM mytable
GROUP BY t_id;
Omesh
  • 27,801
  • 6
  • 42
  • 51
  • That would replace any double commas (,,) with a single one (,) but that isn't my problem. If the i_id is null I end up with a result such a 2,6,8, when what I need is 2,6,8 – Pandy Legend Oct 08 '12 at 12:40
  • The problem is that if there are duplicates between the 2 fields (s_id and i_id), the distinct on each separate field is not going to help – Christophe Vidal Mar 13 '19 at 09:07