SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 29 of 29
  1. #26
    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
    i have two things to say:

    1. what happened when you tested it?

    It returned all rows from Job Types (separated by commas) but there is in fact only one row it should have fetched.

    2. i don't think so
    It seemed to run pretty quick (0.0010 sec).

    Here's the new query I used:

    Code:
    SELECT jobs . * , l.location, j.job_type, i.industry, o.occupation, count( featured_jobs.id ) AS featured, count( job_of_week.id ) AS jobofweek
    FROM jobs
    LEFT OUTER JOIN featured_jobs ON featured_jobs.job_id = jobs.id
    LEFT OUTER JOIN job_of_week ON job_of_week.job_id = jobs.id
    LEFT OUTER JOIN (
    
    SELECT job_id, left( group_concat( sector
    SEPARATOR ', ' ) , 255 ) AS industry
    FROM jobs_sectors
    ) AS i ON i.job_id = jobs.id
    LEFT OUTER JOIN (
    
    SELECT job_id, left( group_concat( location
    SEPARATOR ', ' ) , 255 ) AS location
    FROM jobs_locations
    ) AS l ON l.job_id = jobs.id
    LEFT OUTER JOIN (
    
    SELECT job_id, left( group_concat( job_type
    SEPARATOR ', ' ) , 255 ) AS job_type
    FROM jobs_types
    ) AS j ON j.job_id = jobs.id
    LEFT OUTER JOIN (
    
    SELECT job_id, left( group_concat( occupation
    SEPARATOR ', ' ) , 255 ) AS occupation
    FROM jobs_occupations
    ) AS o ON o.job_id = jobs.id
    WHERE jobs.id = '1003'
    GROUP BY jobs.id
    Thanks for your help.

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Zaggs View Post
    It returned all rows from Job Types (separated by commas) but there is in fact only one row it should have fetched.
    actually it looks like it should have returned all types for the job

    are you sure a job can have multiple types?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    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
    actually it looks like it should have returned all types for the job

    are you sure a job can have multiple types?
    Yes a job can have multiple types but only for that job. It seems to be returning all rows in the jobs_types table (even ones that don't belong to that job).

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ah, yes, i see the problem now

    all your subqueries, with GROUP_CONCATs, are missing their GROUP BY clauses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •