Solved

Deal with null value when using group_concat in mysql

Posted on 2010-09-15
3
474 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
ID: 33680353
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
ID: 33680442
Hi Dal

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

0
 
LVL 16

Author Closing Comment

by:Sheils
ID: 33699186
yes that did the trick. Thanks Mate
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 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

24 Experts available now in Live!

Get 1:1 Help Now