MySQL Group Concat Single Column NULL

Question

I have a column that i am looking to retrieve all matches of in one row. I am querying other data as well. Currently i am using group_concat. This has worked great until now. Sometimes there are potential NULL values in this column and this has been preventing anything from being returned.

i have tried various other solutions posted here without success.

CREATE TABLE table1 (
id mediumint(9) NOT NULL AUTO_INCREMENT,
item_num mediumint(9) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE table2 (
id mediumint(9) NOT NULL AUTO_INCREMENT,
oneid mediumint(9) NOT NULL,
item_desc varchar(16) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

SELECT item_num, GROUP_CONCAT(item_desc) AS alldesc FROM table1 LEFT JOIN table2 ON table1.id = table2.oneid

So basically, there can be several item descripotions that may be NULL; they will be in no particular order either. So i am seeking a list with a placeholder when NULLs arise.

can you show your desired result? – John Woo Nov 05 '12 at 06:45 — John Woo, Nov 05 '12 at 06:45

Yogendra Singh · Accepted Answer · 2012-11-05T06:53:01.130

1

Does this work for you(use description as empty string when it is NULL)?

SELECT item_num, 
       REPLACE(GROUP_CONCAT(IFNULL(item_desc,' ')), ', ,', ',') AS alldesc 
FROM table1 
LEFT JOIN table2 
ON table1.id = table2.oneid

edited Nov 05 '12 at 06:53

answered Nov 05 '12 at 06:46

Yogendra Singh

33,927
6
63
73

tendecy of this is if there are 3 values with `null` you'll get something like this, `hello, world, , , ,` – John Woo Nov 05 '12 at 06:47
im not sure why, but no. if there are no NULLs it works fine. but if there is a null then an item it still gives nothing – The Thirsty Ape Nov 05 '12 at 06:53
@JohnWoo: I added the `REPLACE` method to take care of empty commas. Hope that is fine. – Yogendra Singh Nov 05 '12 at 06:54
@Foo_Chow can you add sample records with desired outout? – John Woo Nov 05 '12 at 06:58
Last one got it; looks like some of the issue was with the php i used to interpret the results too – The Thirsty Ape Nov 05 '12 at 07:01

score 1 · Answer 2 · answered Nov 05 '12 at 07:00

you are missing GROUP BY in your query. chances are if you have multiple item_num, it will always return one row.

SELECT   item_num, GROUP_CONCAT(item_desc) AS alldesc 
FROM     table1 LEFT JOIN table2 
            ON table1.id = table2.oneid
GROUP BY item_num

score 0 · Answer 3 · edited Nov 05 '12 at 07:08

0

Try the following query

SELECT item_num, GROUP_CONCAT(ISNULL(item_desc,'')) AS alldesc FROM table1 LEFT JOIN table2 ON table1.id = table2.oneid

edited Nov 05 '12 at 07:08

Starx

77,474
47
185
261

answered Nov 05 '12 at 06:45

AnandPhadke

13,160
5
26
33

tendecy of this is if there are 3 values with `null` you'll get something like this, `hello, world, , , ,` – John Woo Nov 05 '12 at 06:48

score 0 · Answer 4 · answered Nov 05 '12 at 07:01

SELECT item_num, 
REPLACE(
    GROUP_CONCAT(
      IFNULL(item_desc,'*!*') -- replace this with something not in a normal item_desc
      ORDER BY if(item_desc is null, 1, 0) desc
), '*!*,','') AS alldesc 
FROM table1 
LEFT JOIN table2 
ON table1.id = table2.oneid
GROUP BY item_num

MySQL Group Concat Single Column NULL

4 Answers4