0

I have a long query that returns multiple values for a primary key (from a LEFT join). For example : (only showing two fields, but there about 10 fields)

LotID    Size
1         A
1         B 
1         C
2         null
3         B
4         A
4         B

When I use GROUP_CONACT, it returns as follows : 

LotID       Size
1           A,B,C
3           B
4           A,B

But what I actually want is :

LotID       Size
1           A,B,C
2           null
3           B
4           A,B

I tried using 

GROUP_CONCAT(CONCAT_WS(',', IFNULL(Size,''))) AS Sizes,

It returns :

    LotID       Sizes
    1           A,B,C,,,
    3           B,,
    4           A,B,,

It does not return LotID=2, also aditional commas.

How could I do it to get clean records ?

PCG
  • 2,049
  • 5
  • 24
  • 42
  • Strange but works. MYSQL says that GROUP_CONCAT drops null which I did see too. I will check this again. – PCG Mar 31 '19 at 15:50

1 Answers1

1

You must be doing something wrong with group_concat, because this:

select 
  lotid,
  group_concat(size) size
from tablename
group by lotid

returns:

| lotid | size               |
| ----- | ------------------ |
| 1     | A,B,C              |
| 2     | null               |
| 3     | B                  |
| 4     | A,B                |

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • I think I misunderstood MYSQL instructions "The GROUP_CONCAT function ignores NULL values. It returns NULL if there was no matching row found or all arguments are NULL values." But it still happens in my query. LEFT join returns null (expected), is that could be the issue ? I will post it seperately. – PCG Mar 31 '19 at 15:56
  • If there exists a value of lotid on which you use group_concat then it will return null. Maybe the value you're missing does not exist at all in the query. – forpas Mar 31 '19 at 16:05
  • I added the real query : https://stackoverflow.com/questions/55442980/how-to-group-concat-where-there-are-null-values-in-mysql-will-fields-from-left-j – PCG Mar 31 '19 at 16:26