Multiple COALESCE in GROUP_CONCAT – is showing value if only first column isn’t null

Two table’s purchase and sales ‘s column date column has been concatenated as date row.

GROUP_CONCAT(purchase.date,',',sales.date) AS date

But both of them can be blank, according to data input. So I have concatenated as

GROUP_CONCAT(COALESCE(purchase.date,''),',',COALESCE(sales.date,''))AS date

Here, why if purchase.date is empty and sales.date has value, sales.date column is also fetched as empty ? but in case of purchase.date, value is shown even if sales.date is empty.

I mean my code is not working for second column of GROUP_CONCAT. Second column value is only showed if first one is not empty.

I tried with

GROUP_CONCAT(COALESCE(purchase.date,',',sales.date),'')AS date

GROUP_CONCAT(COALESCE(purchase.date,',',sales.date,'')AS date

but still second column is showing null though it has value. if I move sales.date in first position, it shows value.

Here is fiddle. date shows value because, sales.date is in first position, date1 returns empty because sales.date is in second position.

Sql Fiddle

Answer

This does not do what you seem to want it to do:

COALESCE(sales.date, ',', purchase.date)

COALESCE() returns the first of the three values that is not NULL, so you will never get purchase.date: ',' is never NULL.

You basically want:

GROUP_CONCAT(purchase.date, ',', sales.date) AS date

However, you have learned that CONCAT() — and even this implicit concatenate — return NULL if any of the values are NULL. One solution is individual COALESCE() — as in your answer. I cannot reproduce your problem. Here is an example of the code working.

Or, you can use CONCAT_WS() (although you miss the separator):

GROUP_CONCAT(CONCAT_WS(',', sales.date, purchase.date))