MySQL GROUP_CONCAT that actually registers NULLs?

I have this large, procedurally generated MySQL query. At a part of this query I have to combined sever cells into one cell This is because the person may have more than one form entry (in the case of the same data I was given it's cars, houses, bikes, and strollers). I can use GROUP_CONCAT to make it one field, each separated and that works pretty well, but theres a problem.

Some people don't have all the information filled out so let take the cars for example, there are three of them, but one 1 has a picture, and it happens to be the third one. So I all the results but not in the original order

what I have in the table

ID# | OWNERID# | make    | model     | year | picture
----+----------+---------+-----------+------+-------------
1   | 3        | corolla | gt-s      | 1986 |
2   | 3        | fit     | V6        | 2001 |
3   | 3        | starlet | GT        | 1981 | car.jpg
4   | 4        | F-150   | SuperDuty | 2010 |
5   | 5        | beetle  |           | 1968 |

when I run my GROUP_CONCAT query I get this

ID# | OWNERID# | make                | model      | year           | picture
----+----------+---------------------+------------+----------------+-------------
1   | 3,3,3    | corolla,fit,starlet | gt-s,V6,GT | 1986,2001,1981 | car.jpg
4   | 4        | F-150               | SuperDuty  | 2010           |
5   | 5        | beetle              |            | 1968           |

but I want to get this

ID# | OWNERID# | make                | model      | year           | picture
----+----------+---------------------+------------+----------------+-------------
1   | 3,3,3    | corolla,fit,starlet | gt-s,V6,GT | 1986,2001,1981 | ,,car.jpg
4   | 4        | F-150               | SuperDuty  | 2010           |
5   | 5        | beetle              |            | 1968           |

note the two commas in from of the "carimage.jpg"

The problem seems to be focused around the very first part of the query SELECT GROUP_CONCAT(cfv.value) FROM cf_customfield_values cfv. I've tried a few things to make it act the way I need it to.

Using an IFNULL inside the GROUP_CONCAT to change null values to something else

Moving IFNULL around a bit seeing if I could get a better result

Using a traditional IF statement to change nulls to something else

Using COALESCE to make sure there was always at least 1 non-NULL

Concating a string to the beginning and/or end of the value so that it was always had a sting and therefore was not NULL

I even tried making it GROUP_CONCAT('test'), which made it clear that it really doesn't matter what is in there it will still realize it's a NULL and it will not concat it.

The reason I really want this done was so I could then break down t开发者_运维百科he concated values into sub tables for display (obviously I would be using something more than a comma to prevent any possible screw ups on the display scripts part).

Wow, thats was longer than I though it would be, TLDR; I need GROUP_CONCAT to concat nulls the same as values.

---

FULL SQL

SELECT *, pregi.ttl AS 'general_information-name' , (SELECT GROUP_CONCAT(DISTINCT '[subtablerow]',CONCAT('[row num="',proffv.profileid,'"]',proffv.value),'[/subtablerow]') FROM cf_profiles_fields_values proffv LEFT JOIN cf_profiles_fields proff ON proffv.fieldid=proff.id LEFT JOIN cf_profiles prof ON prof.id=proffv.profileid WHERE proff.id='1') AS 'profile_first_name' , (SELECT GROUP_CONCAT(DISTINCT '[subtablerow]',CONCAT('[row num="',proffv.profileid,'"]',proffv.value),'[/subtablerow]') FROM cf_profiles_fields_values proffv LEFT JOIN cf_profiles_fields proff ON proffv.fieldid=proff.id LEFT JOIN cf_profiles prof ON prof.id=proffv.profileid WHERE proff.id='2') AS 'profile_last_name' , (SELECT GROUP_CONCAT(DISTINCT '[subtablerow]',CONCAT('[row num="',proffv.profileid,'"]',proffv.value),'[/subtablerow]') FROM cf_profiles_fields_values proffv LEFT JOIN cf_profiles_fields proff ON proffv.fieldid=proff.id LEFT JOIN cf_profiles prof ON prof.id=proffv.profileid WHERE proff.id='3') AS 'profile_e-mail_address' , (SELECT GROUP_CONCAT(DISTINCT '[subtablerow]',CONCAT('[row num="',proffv.profileid,'"]',proffv.value),'[/subtablerow]') FROM cf_profiles_fields_values proffv LEFT JOIN cf_profiles_fields proff ON proffv.fieldid=proff.id LEFT JOIN cf_profiles prof ON prof.id=proffv.profileid WHERE proff.id='4') AS 'profile_phone_-_office' , (SELECT GROUP_CONCAT(DISTINCT '[subtablerow]',CONCAT('[row num="',proffv.profileid,'"]',proffv.value),'[/subtablerow]') FROM cf_profiles_fields_values proffv LEFT JOIN cf_profiles_fields proff ON proffv.fieldid=proff.id LEFT JOIN cf_profiles prof ON prof.id=proffv.profileid WHERE proff.id='6') AS 'profile_fax' , (SELECT GROUP_CONCAT(DISTINCT '[subtablerow]',CONCAT('[row num="',proffv.profileid,'"]',proffv.value),'[/subtablerow]') FROM cf_profiles_fields_values proffv LEFT JOIN cf_profiles_fields proff ON proffv.fieldid=proff.id LEFT JOIN cf_profiles prof ON prof.id=proffv.profileid WHERE proff.id='7') AS 'profile_password' , (SELECT GROUP_CONCAT(DISTINCT '[subtablerow]',CONCAT('[row num="',proffv.profileid,'"]',proffv.value),'[/subtablerow]') FROM cf_profiles_fields_values proffv LEFT JOIN cf_profiles_fields proff ON proffv.fieldid=proff.id LEFT JOIN cf_profiles prof ON prof.id=proffv.profileid WHERE proff.id='9') AS 'profile_phone_-_cell' , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=9 AND cfv.memberid=core.id) AS bicycles_build_material , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=10 AND cfv.memberid=core.id) AS bicycles_color , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=7 AND cfv.memberid=core.id) AS bicycles_frame_size , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=8 AND cfv.memberid=core.id) AS bicycles_wheel_size , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=4 AND cfv.memberid=core.id) AS cars_exterior_color , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=5 AND cfv.memberid=core.id) AS cars_interior_color , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=18 AND cfv.memberid=core.id) AS cars_interior_color , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=17 AND cfv.memberid=core.id) AS cars_license , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=1 AND cfv.memberid=core.id) AS cars_make , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=2 AND cfv.memberid=core.id) AS cars_model , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=14 AND cfv.memberid=core.id) AS cars_picture , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=6 AND cfv.memberid=core.id) AS cars_upholstery_material , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=15 AND cfv.memberid=core.id) AS cars_vehicle_history , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=16 AND cfv.memberid=core.id) AS cars_vin , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=3 AND cfv.memberid=core.id) AS cars_year , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=21 AND cfv.memberid=core.id) AS contact_info_email_address , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=20 AND cfv.memberid=core.id) AS contact_info_fax , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=19 AND cfv.memberid=core.id) AS contact_info_phone , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=22 AND cfv.memberid=core.id) AS contact_info_url , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=13 AND cfv.memberid=core.id) AS strollers_height , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=11 AND cfv.memberid=core.id) AS strollers_seats , (SELECT GROUP_CONCAT(IFNULL(cfv.value,'test') ) FROM cf_customfield_values cfv WHERE fieldid=12 AND cfv.memberid=core.id) AS strollers_wheels FROM cf_members core LEFT JOIN cf_members pregi ON pregi.id=core.id

---

group_concat(coalesce(cfv.value, ''))