Solved

Deal with null value when using group_concat in mysql

Posted on 2010-09-15
3
469 Views
Last Modified: 2012-05-10
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
Comment
Question by:Sheils
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
DalHorinek earned 500 total points
Comment Utility
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
 
LVL 16

Author Comment

by:Sheils
Comment Utility
Hi Dal

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

0
 
LVL 16

Author Closing Comment

by:Sheils
Comment Utility
yes that did the trick. Thanks Mate
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now