MySQL join by field with comma seperated list

[r937]

@Zaggs

That brings me to my final question…

are you sure?

@Zaggs

If I wanted to perform a search on the sectors and find all rows (jobs) which are listed in more than one sector - how would I do that?

by joining your jobs table to the following subquery before joining to all the other tables you gots already:

SELECT job_id   
  FROM jobs_sectors
 WHERE sector in (22,23)
GROUP
    BY job_id
HAVING COUNT(*) = 2

[Zaggs]

Hi guys!

I am using the following SQL query which is perfectly valid. However, it’s not returning the desired results. The location, job_type, industry and occupation columns are all being returned blank even though a value exists in all of the join tables (i.e. jobs_locations, jobs_types, jobs_sectors and jobs_occupations).

I can’t understand what’s wrong. Does anyone have any ideas?

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, group_concat( sector
SEPARATOR ', ' ) AS industry
FROM jobs_sectors
) AS i ON i.job_id = jobs.id
LEFT OUTER JOIN (

SELECT job_id, group_concat( location
SEPARATOR ', ' ) AS location
FROM jobs_locations
) AS l ON l.job_id = jobs.id
LEFT OUTER JOIN (

SELECT job_id, group_concat( job_type
SEPARATOR ', ' ) AS job_type
FROM jobs_types
) AS j ON j.job_id = jobs.id
LEFT OUTER JOIN (

SELECT job_id, group_concat( occupation
SEPARATOR ', ' ) AS occupation
FROM jobs_occupations
) AS o ON o.job_id = jobs.id
WHERE jobs.id = '1001'
GROUP BY jobs.id

[r937]

instead of GROUP_CONCAT(…), try LEFT(GROUP_CONCAT(…),255)

[Zaggs]

@r937

instead of GROUP_CONCAT(…), try LEFT(GROUP_CONCAT(…),255)

Does that slow the whole query down though? I need this query to be very fast as it will be searching a lot of results.

[r937]

@Zaggs

Does that slow the whole query down though?

i have two things to say:

1. what happened when you tested it?

2. i don’t think so

[Zaggs]

@r937

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:

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.

[r937]

@Zaggs

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?

[Zaggs]

@r937

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).

[r937]

ah, yes, i see the problem now

all your subqueries, with GROUP_CONCATs, are missing their GROUP BY clauses