MySQL: handling conditional values within GROUP_CONCAT -

MySQL: handling conditional values within GROUP_CONCAT -

i'm trying retrieve info using left bring together , group_concat(). however, values aren't present, need represent lack of data, or method of identification each, can them in application.

concat_ws(0x1d, group_concat(distinct bcod1.value separator 0x1f), group_concat(distinct bcod2.value separator 0x1f), group_concat(distinct bcod3.value separator 0x1f), group_concat(distinct bcod4.value separator 0x1f)) clients_options

here, 4 instances of group_concat() relate either inner bring together or left bring together in search performed user:

inner bring together bookings_clients_options bco1 on (bco1.client_id = '3') inner bring together bookings_clients_options_data bcod1 on (bco1.name = 'unit_code') , (bco1.bookings_client_option_id = bcod1.bookings_client_option_id) , (bcod1.value regexp ('.*')) , (bcod1.booking_attendee_id = bookings_attendees.booking_attendee_id) inner bring together bookings_clients_options bco2 on (bco2.client_id = '3') inner bring together bookings_clients_options_data bcod2 on (bco2.name = 'creditor_ev_number') , (bco2.bookings_client_option_id = bcod2.bookings_client_option_id) , (bcod2.value regexp ('.*')) , (bcod2.booking_attendee_id = bookings_attendees.booking_attendee_id) inner bring together bookings_clients_options bco3 on (bco3.client_id = '3') left bring together bookings_clients_options_data bcod3 on (bco3.name = 'purchase_order_number') , (bco3.bookings_client_option_id = bcod3.bookings_client_option_id) , (bcod3.booking_attendee_id = bookings_attendees.booking_attendee_id) inner bring together bookings_clients_options bco4 on (bco4.client_id = '3') inner bring together bookings_clients_options_data bcod4 on (bco4.name = 'purchase_order_booking') , (bco4.bookings_client_option_id = bcod4.bookings_client_option_id) , (bcod4.value regexp ('y')) , (bcod4.booking_attendee_id = bookings_attendees.booking_attendee_id)

here, bcod3.value results "collapse" in if there's no value bcod3.value value bcod4.value, bcod4.value drops space bcod3.value.

as can see, each of these columns has name, having tried...

group_concat(distinct bcod" . $x . ".value separator 0x1f) unit_code

... got error surrounding concat_ws().

i tried...

if(bcod" . $x . ".value = '', 'qqq', group_concat(distinct bcod" . $x . ".value separator 0x1f))

... doesn't appear anything.

i tried...

group_concat(distinct if(bcod" . $x . ".value = 0, 'qqq', bcod" . $x . ".value) separator 0x1f)

.. , while did grab instances of bcod3.value, grabbed of instances of bcod1.value have values not '0'.

i'm not 100% sure understand question, think do. sample info , desired results worth 1000 words of explanation.

if problem null or blank values beingness ignored, do:

concat_ws(0x1d, coalesce(group_concat(distinct bcod1.value separator 0x1f), ''), coalesce(group_concat(distinct bcod2.value separator 0x1f), ''), coalesce(group_concat(distinct bcod3.value separator 0x1f), ''), coalesce(group_concat(distinct bcod4.value separator 0x1f), '') ) clients_options

it considered benefit group_concat() , concat_ws() ignore null values. however, if want them, utilize coalesce().

mysql