SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    Code:
    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?

  2. #2
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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

    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 --
    Code:
      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

    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



    finally, always use indentation in your SQL, it makes it a lot easier to read...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

    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 --
    Code:
      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

    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



    finally, always use indentation in your SQL, it makes it a lot easier to read...
    Code:
    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 :-)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •