CODEWITHSUNDEEP
mysqlsqlgroup-concat
AnandaLC
AnandaLC

Reputation: 43

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

Upvotes: 0

Views: 745

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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))

Upvotes: 1

AnandaLC
AnandaLC

Reputation: 43

I am not sure only COALESCE is enough here, as @TimBiegeleisen said - COALESCE always returns the first non NULL value in its list. So I apply CASE condition

  CASE
    WHEN purchase.date IS NULL THEN GROUP_CONCAT(COALESCE(sales.date,'')) 
    WHEN sales.date IS NULL THEN GROUP_CONCAT(COALESCE(purchase.date,'')) 
    WHEN purchase.date !='' AND sales.date !='' THEN GROUP_CONCAT(COALESCE(purchase.date,',',sales.date),'')
  END AS date1

Upvotes: 0

Related Questions