MySQL query does not return desired results

Databases
1

Hi Guys!

I have the following SQL query and it’s not returning the desired results. I think it has something to do with the where clause (in particular the l.postcode field).

Here is some data that I have inside the jobs_locations table:


1010 	1001 	CM14 	Brentwood, Essex
1010 	1001 	IG7 	Chigwell, Essex
1010 	1001 	SE2 	London
1010 	1001 	TW8 	Brentford, Middlesex

Given the data the SQL query should return the second row but it doesn’t. Any ideas?


SELECT jobs.job_title, jobs.job_url, jobs.job_salary, jobs.job_ote, jobs.job_perks, jobs.job_description, jobs.date_posted, companies.company_name, companies.company_url, companies.logo_thumbnail, jobs_industries.industry_id, jobs_job_types.job_type_id, count( applications.id ) AS applications, count( featured_jobs.id ) AS featured, l.display_name, l.postcode
FROM jobs
INNER JOIN companies ON companies.id = jobs.company_id
LEFT OUTER JOIN applications ON applications.job_id = jobs.id
LEFT OUTER JOIN featured_jobs ON featured_jobs.job_id = jobs.id
INNER JOIN jobs_industries ON jobs_industries.job_id = jobs.id
INNER JOIN jobs_job_types ON jobs_job_types.job_id = jobs.id
INNER JOIN (
SELECT job_id, if( count( job_id ) >1, 'Locations throughout the UK', display_name ) AS display_name, postcode
FROM jobs_locations
GROUP BY job_id
) AS l ON l.job_id = jobs.id
WHERE jobs.status = 'on'
AND l.postcode = 'IG7'
GROUP BY jobs.id
ORDER BY featured DESC , date_posted DESC
2

Do your results vary if you change the WHERE clause to this?


WHERE l.postcode = 'IG7' AND jobs.status = 'on'
3

Thanks for your reply, but unfortunately get the same result (no rows returned).

4

I always put all INNER JOINs first, and LEFT JOINs last, so I would write that query like this:


SELECT jobs.job_title, jobs.job_url, jobs.job_salary, jobs.job_ote, jobs.job_perks, jobs.job_description, jobs.date_posted, companies.company_name, companies.company_url, companies.logo_thumbnail, jobs_industries.industry_id, jobs_job_types.job_type_id, count( applications.id ) AS applications, count( featured_jobs.id ) AS featured, l.display_name, l.postcode
FROM jobs
INNER JOIN companies ON companies.id = jobs.company_id
INNER JOIN jobs_industries ON jobs_industries.job_id = jobs.id
INNER JOIN jobs_job_types ON jobs_job_types.job_id = jobs.id
INNER JOIN (
  SELECT job_id, if( count( job_id ) >1, 'Locations throughout the UK', display_name ) AS display_name, postcode
  FROM jobs_locations
  GROUP BY job_id
) AS l ON l.job_id = jobs.id
LEFT OUTER JOIN applications ON applications.job_id = jobs.id
LEFT OUTER JOIN featured_jobs ON featured_jobs.job_id = jobs.id
WHERE jobs.status = 'on'
AND l.postcode = 'IG7'
GROUP BY jobs.id
ORDER BY featured DESC , date_posted DESC

No idea if it makes a difference though.

5

Thanks for the post, unfortunately it didn’t work though.

I think it’s because I have multiple rows in the jobs_locations table. When the postcode is CM14 for example it works (maybe because it’s the first row). All other postcodes do not work.

6

Are you sure you have related data in all joined tables?
In phpMyAdmin, you could start with a query on the jobs table, and then add the joins one by one to see which one is causing the postcode ‘IG7’ not to return any rows.

7

and probably in other tables as well

your counts are either inflated or identical or both, right?

8

I managed to strip it down to this, but still getting no rows…

SELECT jobs.job_title, jobs.job_url, jobs.job_salary, jobs.job_ote, jobs.job_perks, jobs.job_description, jobs.date_posted, l.display_name, l.postcode
FROM jobs
INNER JOIN (

SELECT job_id, if( count( job_id ) >1, 'Locations throughout the UK', display_name ) AS display_name, postcode
FROM jobs_locations
GROUP BY job_id
) AS l ON l.job_id = jobs.id
WHERE l.postcode = 'IG7'
GROUP BY jobs.id
ORDER BY date_posted DESC
LIMIT 0 , 30

Rudy, im really not sure what you mean … :frowning:

9

ah, no rows… okay

(but you may yet encounter the inflated or duplicate counts)

do you have any rows with a 3-letter postcode?

10

Run only the inner query, and see if you get a row with the postcode ‘IG7’


SELECT job_id, if( count( job_id ) >1, 'Locations throughout the UK', display_name ) AS display_name, postcode
FROM jobs_locations
GROUP BY job_id

Grouping by job_id, if there are multiple rows with different postcodes for the same job id, it’ll return only one postcode, and who knows which one.

11

Yes, there may even be rows with 2 letter postcodes - for example W1

Yes it returns CM14

12 
WHERE l.postcode = 'IG7'

‘CM14’ <> ‘IG7’

So no rows.

13

Sorry, not quite sure what this means…

14

In your query you have this check:


WHERE l.postcode = 'IG7'

And you just told us that this subquery


SELECT job_id, if( count( job_id ) >1, 'Locations throughout the UK', display_name ) AS display_name, postcode
FROM jobs_locations
GROUP BY job_id

returns the postcode ‘CM14’.

So l.postcode = ‘CM14’, not ‘IG7’, so that check doesn’t validate, so no rows are returned.

15

Got you :slight_smile: Thanks.

16

Hi again guys!

I changed a few things around and now I have the new query below. It works well. The only thing is it doesn’t give me a list of all display_name rows matching the job id - it always just returns the 1 (for example Brentwood, Essex). This means that my if statement never gets executed.

if( count( job_id ) >1, 'Locations throughout the UK', display_name ) AS display_name

How could I achieve this?


SELECT 
	jobs.job_title, 
    jobs.job_url, 
    jobs.job_salary, 
    jobs.job_ote, 
    jobs.job_perks, 
    jobs.job_description, 
    jobs.date_posted, 
    companies.company_name, 
    companies.company_url, 
    companies.logo_thumbnail, 
    count( applications.id ) AS applications, 
    count( featured_jobs.id ) AS featured, 
    l.display_name, 
    l.postcode, 
    l.display_county
FROM jobs
INNER JOIN companies ON companies.id = jobs.company_id
INNER JOIN jobs_industries ON jobs_industries.job_id = jobs.id
INNER JOIN jobs_job_types ON jobs_job_types.job_id = jobs.id
INNER JOIN (
    SELECT job_id, if( count( job_id ) >1, 'Locations throughout the UK', display_name ) AS display_name, postcode, display_county
    FROM jobs_locations
    GROUP BY postcode
) AS l ON l.job_id = jobs.id
	LEFT OUTER JOIN applications ON applications.job_id = jobs.id
	LEFT OUTER JOIN featured_jobs ON featured_jobs.job_id = jobs.id
WHERE jobs.status = 'on'
AND l.postcode = 'CM14'
GROUP BY jobs.id
ORDER BY featured DESC , date_posted DESC
17

by not misusing the GROUP BY clause


SELECT job_id, if( count( job_id ) >1, 'Locations throughout the UK', display_name ) AS display_name, postcode, display_county
    FROM jobs_locations
    GROUP BY postcode

this query produces one row per postcode

which job_id do you think it will return for each postcode?