Help with group_concat and count

Hi Guys!

Is it possible to have a query that if the count is bigger than 1 it will put the value “More than 1” otherwise it will do a GROUP_CONCAT.

For example, take this query:

SELECT jobs.id, jobs.job_title, jobs.job_url, companies.company_name, companies.company_url, l.display_name
FROM jobs
LEFT JOIN companies ON companies.id = jobs.company_id
INNER JOIN (
	SELECT job_id, group_concat( display_name SEPARATOR ', ' ) AS display_name 
    FROM jobs_locations 
    GROUP BY job_id
) AS l ON l.job_id = jobs.id
WHERE companies.status = 'Active'
ORDER BY jobs.date_posted DESC

Now, what I want to do is - if the count of rows on the INNER JOIN (jobs_locations) is bigger than 1, then it will pre populate with “More than 1”, otherwise it will do the GROUP_CONCAT.

Is this possible?

Solution:

SELECT jobs.id, jobs.job_title, jobs.job_url, companies.company_name, companies.company_url, l.display_name
FROM jobs
LEFT JOIN companies ON companies.id = jobs.company_id
INNER JOIN (

SELECT job_id, IF( count( job_id ) >1, ‘More than 1’, display_name ) AS display_name
FROM jobs_locations
GROUP BY job_id
) AS l ON l.job_id = jobs.id
WHERE companies.status = ‘Active’
ORDER BY jobs.date_posted DESC

that IF is a nice little trick, wouldn’t work exactly like that in any other database except mysql, but could be made to work by using CASE instead of IF and MIN on the display_name even though there would be only one

if you don’t understand that remark, don’t worry :slight_smile:

anyhow, i wanted to point out something else more important that you should be aware of

a LEFT OUTER JOIN is used whenever you want to return all rows of the left table with or without matching rows from the right table

so let’s look at this –


  FROM jobs
 LEFT OUTER
  JOIN companies 
    ON companies.id = jobs.company_id

this says you expect there might be some rows in the jobs table that don’t have a matching row in the companies table

in other words, you want to include jobs for companies that don’t exist

is that realistic?

i didn’t think so :slight_smile:

also, here’s another point – suppose that there actually are some jobs which don’t have a matching company, and you do want a LEFT OUTER JOIN

as you know, in a LEFT OUTER JOIN any rows in the result set for unmatched jobs will have all the columns in the result row that come from the companies table set to NULL

but then the WHERE clause takes over, and completely sabotages the “outerness” of the join by requiring that the company status have a non-NULL value

if you do really want to include jobs without companies in the result, then you need to move the WHERE condition into the ON clause of the join

but i suspect that what you really want is an inner join

:slight_smile:

finally, always use indentation in your SQL, it makes it a lot easier to read…


SELECT jobs.id
     , jobs.job_title
     , jobs.job_url
     , companies.company_name
     , companies.company_url
     , locs.display_name
   FROM jobs
 INNER
   JOIN companies
     ON companies.id = jobs.company_id
    AND companies.status = 'Active'
 INNER
   JOIN ( SELECT job_id
               , CASE WHEN COUNT(*) > 1
                      THEN 'More than 1'
                      ELSE MIN(display_name) 
                  END AS display_name
            FROM jobs_locations
          GROUP 
              BY job_id ) AS locs
    ON locs.job_id = jobs.id
ORDER 
    BY jobs.date_posted DESC

Noted, thank you very much for your advice :slight_smile: