GROUP_CONCAT pulling out empty result with delimiters

Question

I asked a question yesterday on how to pull out multiple results into one field and was given the answer GROUP_CONTACT().

I've put this into my code and it works fine. I need to do this for two fields and thus I have now started to use it twice in the same sql statement. Unfortunately, it's pulling back an empty list for the second field with commas and I'm not too sure why.

Here is my sample product data:

pid || prod
1   || top
2   || sweater

Here is my sample stock data (some stock doesn't have two sizes e.g. waist and chest):

sid || size1 || size2 || pid
1   || M     ||       || 1
2   || L     ||       || 1
3   || XL    ||       || 1
4   || L     ||       || 2
5   || XL    ||       || 2

Here is my code:

SELECT p.id, GROUP_CONCAT(s.size1) size1, GROUP_CONCAT(s.size2) size2, p.prod
FROM products p JOIN stock s ON s.prodid = p.id

This is what it should bring out:

pid || size1  || size2 || prod
1   || M,L,XL ||       || top
2   || L,XL   ||       || sweater

This is what it is actually bringing out:

pid || size1  || size2 || prod
1   || M,L,XL || ,,    || top
2   || L,XL   || ,     || sweater

I've checked to see if there is a space or anything in size2 and there is nothing in there.

I did this query and the product came back as I expected:

SELECT size1, size2 FROM stock WHERE pid = 1 AND size2 = ""

When I did this query, nothing came back:

SELECT size1, size2 FROM stock WHERE pid = 1 AND size2 IS NULL

I know GROUP_CONCAT() will ignore NULL results but I need to do something to stop GROUP_CONTACT() from showing an empty comma delimited list when it is just "" rather than NULL.

Answer 1

SELECT p.id, GROUP_CONCAT(s.size1) size1, 
GROUP_CONCAT(if (s.size2 ='', null, s.size2)) as size2, p.prod 
FROM products p JOIN stock s ON s.prodid = p.id

Answer 2

You can also use the NULLIF() function to convert empty strings to NULLs:

SELECT
  p.id,
  GROUP_CONCAT(       s.size1     ) AS size1,
  GROUP_CONCAT(NULLIF(s.size2, '')) AS size2,
  p.prod
FROM products AS p
  INNER JOIN stock AS s ON s.prodid = p.id
GROUP BY
  p.id,
  p.prod
;