Sheils
asked on
Deal with null value when using group_concat in mysql
I have the following query in mysql views:
select distinct `tblApplications`.`fldAppl icationID` AS `fldApplicationID`,`tblLot onPlan`.`f ldLotonPla nID` AS `fldLotonPlanID`,
group_concat(_latin1' ',`tblEntity`.`fldName`,' ',`tblEntity`.`fldSurname` ,_latin1' ' separator ',') AS `Owners`
from (((`tbl_Lnk_Applications`
join `tblLotonPlan` on`tbl_Lnk_Applications`.` fldApplica tionLinkID ` = `tblLotonPlan`.`fldApplica tionLinkID `)
join `tblApplications` on`tblApplications`.`fldAp plicationI D` = `tbl_Lnk_Applications`.`fl dApplicati onID`)
join `tblLandowner` on `tblLandowner`.`fldLotonPl anID` = `tblLotonPlan`.`fldLotonPl anID`)
join `tblEntity` on`tblEntity`.`fldEntityID ` = `tblLandowner`.`fldEntityI D`
group by `tblLotonPlan`.`fldLotonPl anID`
The main issue is the field
group_concat(_latin1' ',`tblEntity`.`fldName`,' ',`tblEntity`.`fldSurname` ,_latin1' ' separator ',') AS `Owners
This fields return a null value when `tblEntity`.`fldSurname` is null such as in the case of a business name.
I have tried using
group_concat(_latin1' ',`tblEntity`.`fldName`,' ',IFNULL(`tblEntity`.`fldS urname`,"" ),_latin1' ' separator ',') AS `Owners
Problem with this approach is that it repeats an instant of the name for each space in the name. For example:
Expert Exchange Forum will return
Expert Exchange Forum,Expert Exchange Forum,Expert Exchange Forum
How do I deal with this
select distinct `tblApplications`.`fldAppl
group_concat(_latin1' ',`tblEntity`.`fldName`,' ',`tblEntity`.`fldSurname`
from (((`tbl_Lnk_Applications`
join `tblLotonPlan` on`tbl_Lnk_Applications`.`
join `tblApplications` on`tblApplications`.`fldAp
join `tblLandowner` on `tblLandowner`.`fldLotonPl
join `tblEntity` on`tblEntity`.`fldEntityID
group by `tblLotonPlan`.`fldLotonPl
The main issue is the field
group_concat(_latin1' ',`tblEntity`.`fldName`,' ',`tblEntity`.`fldSurname`
This fields return a null value when `tblEntity`.`fldSurname` is null such as in the case of a business name.
I have tried using
group_concat(_latin1' ',`tblEntity`.`fldName`,' ',IFNULL(`tblEntity`.`fldS
Problem with this approach is that it repeats an instant of the name for each space in the name. For example:
Expert Exchange Forum will return
Expert Exchange Forum,Expert Exchange Forum,Expert Exchange Forum
How do I deal with this
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes that did the trick. Thanks Mate
ASKER
I will try this out at work tomorrow and get back to you.