Concat_WS & Coalesce

Tips submitted by PHPMaker users
Post Reply
randy
User
Posts: 20

Concat_WS & Coalesce

Post by randy »

Issue of groups for report headers wanted multiple fields in group report form only gives five or so.
used CONCAT_WS( ) and COALESCE() to make a group filed joining multiple fields and take care of Null fields - Null in any fd otherwise returns NULL only.

SELECT Concat_WS(CONVERT(Coalesce(tbl_pt_main.Dep_name, ' ') USING utf8),
Coalesce(tblrelation_master.relation, ' '),
Coalesce(tbl
service_master.service_no, ' '),
CONVERT(Coalesce(tbl_pt_main.service_number, ' ') USING utf8),
CONVERT(Coalesce(tbl_pt_main.first_name_service_pers, ' ') USING utf8),
CONVERT(Coalesce(tbl_pt_main.last_name_service_pers, ' ') USING
utf8)) AS Details,
tbl_testmaster.Test_Name AS Test_Name,
tbl_pt_reps.Test_result AS Test_result,
tbl_testmaster.units AS units,
tbl_testmaster.nor_adult_male AS nor_adult_male,
tbl_testmaster.nor_adult_female AS nor_adult_female,
tbl_pt_reps.Remarks AS Remarks,
tbl_pt_reps.dt_order AS dt_order,
tbl_pt_main.hid AS hid,
tbl_pt_reps.id AS id,
tblunit_master.unit AS unit
FROM ((((tbl_pt_main
JOIN tbl_pt_reps ON tbl_pt_main.hid = tbl_pt_reps.hid)
JOIN tbl_testmaster ON tbl_testmaster.id = tbl_pt_reps.id_tbl_tdets AND
tbl_testmaster.id_path_sec = tbl_pt_reps.sec_id)
JOIN tbl
unit_master ON tblunit_master.id = tbl_pt_main.unit)
JOIN tbl
service_master ON tblservice_master.sid =
tbl_pt_main.service_no_prefix)
JOIN tbl
relation_master ON tbl__relation_master.rel_no =
tbl_pt_main.relation

Works.


Post Reply