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

I'm confused. If you add a `where` clause limiting `SpecialtyName`, why are you concerned that only those specialties are included in the results? — Gordon Linoff, May 22 '14 at 11:23
Hi @Gordon. The job results are correctly limited by the 'where' clause, but 'specialtyNames' only shows one 'SpecialtyName' and not the other 'specialtyNames' of this particular job. — Fred, May 22 '14 at 11:41

score 0 · Answer 1 · answered May 22 '14 at 10:09

I suppose that using aliases for your tables and subqueries could resolve your problem. You can try something like this:

SELECT 

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

tblCompanies.companyID, 
tblCompanies.companyName, 
tblCompanies.companyNameConvert,  
(SELECT GROUP_CONCAT(DISTINCT ts.specialtyName 
        ORDER BY FIELD (
            specialtyName, 
            'specialtyName1', 
            'specialtyName2', 
            'specialtyName3'), 
            specialtyName ASC) 
        FROM tblSpecialties ts) AS specialtyNames , 
, ... ,
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

I didn't tested this code, but It could help.

Do you have any other suggestions? Do you think the problem lies in the GROUP_CONCAT or in the condition? Thanks for your time. — Fred, May 22 '14 at 10:50
Have you tried to put an alias for the tables in the main query, too? for example: ...LEFT JOIN tblSpecialities tblS ... — kiks73, May 22 '14 at 12:04

score 0 · Accepted Answer · answered May 22 '14 at 11:54

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.

Hi @Gordon, that's it! Thank you very much. Completely unfamiliar with 'HAVING' but not anymore. — Fred, May 22 '14 at 13:38

Mysql group_concat with distinct and where gives strange results

2 Answers2