Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2005
    Posts
    7

    Unanswered: mysql column represent a dynamic query?

    Here's an example:

    we have two tables, one is called job_categories which has two columns:
    job_category_id (integer, primary key, auto increment)
    job_category_title (title of the job category)

    second table is titled jobs, which is a list of jobs. One of the columns is job_category_id to correspond to the category it belongs in. Say we want to get a query that will list each job category title individually, and then how many jobs exist in the jobs table with that category_id. i.e

    Architecture (3)
    Human Resources (8)
    Information Technology (0)
    Miscellaneous (19)

    Is there any way to do that query without having to do a count query individually on each row returned in some sort of a loop? I don't have any of my SQL books here (I'm at home with the family for Christmas, brought the powerbook with me).

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select job_category_title
         , count(*) as number_of_jobs
      from job_categories 
    left outer
      join jobs
        on jobs.job_category_id = job_categories.job_category_id
    group
        by job_category_title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Posts
    7
    Thanks that worked great. Can you possibly gimme a brief explanation of how that join is working, or have a good reference site handy?

  4. #4
    Join Date
    Dec 2005
    Posts
    7
    Actually, I notice even if 0 records exist for a category that query still returns 1 for each category. Any suggestions?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ooops, my bad

    change count(*) to count(jobs.job_category_id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2005
    Posts
    7
    Thanks, man. Is there a way to limit it to only return locations that have more than 0 jobs associated with that location id?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, change LEFT OUTER JOIN to INNER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2005
    Posts
    7
    Thanks again. One more revisit to this, say I have another table called Employers with an employer_active field (1 or 0) and I want to only have this query represent jobs who have an employer_id of an employer whose employer_active = 1

    table employer has employer_id, employer_active, etc.
    job has job_id, employer_id, etc.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
      from job
    inner
      join employer
        on employer.employer_id = job.employer_id
       and employer_active = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2005
    Posts
    7
    oops I forgot to mention I want this to be represented in the location count as well, i.e

    Say There are 10 jobs listed under maryland, but only 5 are active, then it would show

    job_location_name = maryland
    num_jobs = 5

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    job, categories, employers, locations ... i'm lost now -- what are you counting again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2005
    Posts
    7
    OK I'll try to explain, thanks for your patience!

    Table Jobs has (among others):
    job_id, employer_id, job_active, job_location_id

    table job_locations has (among others):
    job_location_id, job_location_name

    table employers has (among others):
    employer_id, employer_active

    I want to essentially get a list in the format:

    Alabama (2)
    Arkansas (3)
    etc.,

    the count should represent how many jobs have that location id, have job_active = 1, and employer_id of an employer who has employer_active = 1 . Does this make sense?

Posting Permissions

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