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