GROUP_CONCAT pulls an empty delimited result

Question

Yesterday I asked a question about how to output several results in one field and received a response GROUP_CONTACT() .

I put this in my code and it works great. I need to do this for two fields, and so I now started using it twice in the same sql statement. Unfortunately, it discards the empty list for the second field with commas, and I'm not sure why.

Here are my product details:

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

Here are my data on trial stocks (some stocks do not have two sizes, for example, 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 

Here's what you should get:

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

Here is what it actually infers:

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

I checked if there is a space or something in size2 and there is nothing there.

I made this request and the product returned as I expected:

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

When I made this request, nothing happened:

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

I know that GROUP_CONCAT() ignore the results of NULL, but I need to do something to stop GROUP_CONTACT() from displaying an empty comma-separated list when it is just "" and not 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 NULL:

 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 ; 

Answer 3

try the following:

 SELECT p.id, GROUP_CONCAT(s.size1) size1, GROUP_CONCAT(case when s.size2='' then null else s.size2 end) size2, p.prod FROM products p JOIN stock s ON s.prodid = p.id 

Answer 4

Have you tried using GROUP_CONCAT(DISTINCT column) ? From my understanding of the documentation, this should lead to an empty string as you want.

DISTINCT will eliminate all duplicates, leaving you with only one appearance of "" .