[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

Deal with null value when using group_concat in mysql

I have the following query in mysql views:

select distinct `tblApplications`.`fldApplicationID` AS `fldApplicationID`,`tblLotonPlan`.`fldLotonPlanID` AS `fldLotonPlanID`,
group_concat(_latin1' ',`tblEntity`.`fldName`,' ',`tblEntity`.`fldSurname`,_latin1' ' separator ',') AS `Owners`
from (((`tbl_Lnk_Applications`
join `tblLotonPlan` on`tbl_Lnk_Applications`.`fldApplicationLinkID` = `tblLotonPlan`.`fldApplicationLinkID`)
join `tblApplications` on`tblApplications`.`fldApplicationID` = `tbl_Lnk_Applications`.`fldApplicationID`)
join `tblLandowner` on `tblLandowner`.`fldLotonPlanID` = `tblLotonPlan`.`fldLotonPlanID`)
join `tblEntity` on`tblEntity`.`fldEntityID` = `tblLandowner`.`fldEntityID`
group by `tblLotonPlan`.`fldLotonPlanID`

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`.`fldSurname`,""),_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

0
Sheils
Asked:
Sheils
  • 2
1 Solution
 
DalHorinekCommented:
The problem is that group_concat just takes all occurences and concats them.

Try to add DISTINCT

group_concat(DISTINCT _latin1' ',`tblEntity`.`fldName`,' ',`tblEntity`.`fldSurname`,_latin1' ' separator ',') AS `Owners

Open in new window

0
 
SheilsAuthor Commented:
Hi Dal

I will try this out at work tomorrow and get back to you.

0
 
SheilsAuthor Commented:
yes that did the trick. Thanks Mate
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now