SQL group_concat remove left join

Question

I have these two tables

 FunctionName | value
 -------------+---------
 intensity    |  0
 status       |  NULL

and

 FunctionName | StatusName 
 -------------+------------
 status       |  ON        
 status       |  Off         

I'm using this query:

SELECT 
    Functions.FunctionName, Functions.value,
    GROUP_CONCAT(FunctionsWithStatus.StatusName)
FROM
    Functions
LEFT JOIN
    FunctionsWithStatus ON Functions.FunctionName = FunctionsWithStatus.Functionsname

The result is:

Name   | value | group_concat
status | 0     | off,on

How can I also retrieve the value of "intensity" and get a result like this:

Name      | value | group_concat
intensity |  0    |  NUll
status    |  0    | off,on

Answer 1

Your query is malformed. You have mixed unaggregated columns with aggregated columns. MySQL treats this as an aggregation query with no group by -- which returns exactly one row. The values for the unaggregated columns comes from indeterminate rows.

You should include all unaggregated columns in the group by (not just a good idea, but standard SQL):

select f.FunctionName, f.value, group_concat(fws.StatusName)
from Functions f left join
     FunctionsWithStatus fws
     on f.FunctionName = fws.Functionsname
group by f.FunctionName, f.value;