CODEWITHSUNDEEP
mysqlgroup-concat
David
David

Reputation: 1023

MySQL GROUP_CONCAT with Nulls

Is there an option to make MySQL's Group_Concat function include nulls?

Consider the following example from my source table:

userId, questionId, selectionId
7, 3, NULL
7, 4, 1
7, 5, 2

When I query on the selection table with GROUP_CONCAT, I get the following:

7, 4=1,5=2

I would like to get the following:

7, 3=NULL,4=1,5=2

For reference, my query looks like this:

Select userId, GROUP_CONCAT(CONCAT(questionId, '=', selectionId))
From selection
Group by userId;

I also tried adding an IFNULL like this:

Select userId, GROUP_CONCAT(IFNULL(CONCAT(questionId, '=', selectionId), 'NULL'))
From selection
Group by userId;

but that produced the following:

7, NULL,4=1,5=2

Note - There is one other complexity that I forgot to include. The selectionId is a foreign key to another table. I use a left outer join to the selection_text table. My real query includes fields from that table (these fields resolve to NULL since the selectionId is null).

Upvotes: 44

Views: 46161

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

You should just IFNULL the column that can be NULL;

SELECT userId, GROUP_CONCAT(CONCAT(questionId, '=', 
                 IFNULL(selectionId, 'NULL')))
FROM selection
GROUP BY userId;

Demo here.

Upvotes: 66

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34581

You should use IFNULL or COALESCE on the selectionId value directly:

SELECT
  userId,
  GROUP_CONCAT(CONCAT(questionId, '=', COALESCE(selectionId, 'NULL')))
FROM selection
GROUP BY userId;

Upvotes: 7

Related Questions