Mysql group_concat with distinct and where gives strange results

Question

I have the following query which works fine (see below).

But when I add a condition, for example AND (specialtyName = '...') the main results are fine, but the GROUP_CONCAT only shows the results that match the condition.

Can anyone please help me with this?

Thanks in advance.

Fred.

SELECT 

    tblJobs.jobID, 
    tblJobs.jobName, 
    DATE_FORMAT(tblJobs.jobDate,'%d-%m-%Y'), 

    tblCompanies.companyID, 
    tblCompanies.companyName, 
    tblCompanies.companyNameConvert, 

    GROUP_CONCAT(DISTINCT tblSpecialties.specialtyName 
        ORDER BY FIELD (
            specialtyName, 
            'specialtyName1', 
            'specialtyName2', 
            'specialtyName3'), 
            specialtyName ASC) 
        AS specialtyNames, 

    GROUP_CONCAT(DISTINCT tblSpecialties.specialtyNameConvert 
        ORDER BY FIELD (
            specialtyName, 
            'specialtyName1', 
            'specialtyName2', 
            'specialtyName3'), 
            specialtyName ASC) 
        AS specialtyNamesConvert, 

    GROUP_CONCAT(DISTINCT tblRegions.regionName), 
    GROUP_CONCAT(DISTINCT tblRegions.regionNameConvert) 

FROM tblJobs 

LEFT JOIN tblCompanies ON 
(tblJobs.jobCompany = tblCompanies.companyID) 

LEFT JOIN tblSpecialties ON 
FIND_IN_SET(tblSpecialties.specialtyID, REPLACE(tblJobs.jobSpecialty,' ',',')) 

LEFT JOIN tblRegions ON 
FIND_IN_SET(tblRegions.regionID, REPLACE(tblJobs.jobRegion,' ',','))

WHERE 

    AND jobActive = '1' 
    AND jobDate >= '2013-01-01' 
    AND companyActive = '1' 

GROUP BY jobID 
ORDER BY jobDate DESC, jobID DESC, jobCompany DESC

Answer 1

If you say:

WHERE jobActive = '1' AND jobDate >= '2013-01-01' AND companyActive = '1' AND
      specialties = XXX

Then you are only going to get exactly those specialties. The filtering is done before the aggregation. As a note: including such conditions in the where clause also turns the outer joins to inner joins. Your joins are probably on properly aligned foreign key relationships, so inner joins may be appropriate.

I'm guessing what you really want is to filter jobs by those having that specialty, but to keep all other information. You want to do the filtering after the aggregation. Do this with a having clause instead of a where clause:

having sum(specialties = XXX) > 0;

This will keep only the rows that have the particular specialty, and keep all the other information.