Inner join count is all the same

Hi Guys!

I have the following query, but the value of display_name is always 1. Any idea why this might be? Just to clarify, I have multiple rows in the jobs_locations database (with same job_id and different postcode values).

Here’s the query:

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, 
        	count(job_id) 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 like 'CM6%' or l.postcode like 'CM22%' or l.postcode like 'CM7%' or l.postcode like 'CM24%' or l.postcode like 'CM77%' or l.postcode like 'CM1%' or l.postcode like 'CM23%') 
group by jobs.id 
order by featured desc, date_posted desc

Thanks in advance.

please show the first few results of this query -


SELECT job_id
     , 1 as display_name
     , postcode
     , display_county 
  FROM jobs_locations 
ORDER 
    BY postcode

then please show the first few results of this query -


SELECT job_id
     , COUNT(job_id) as display_name
     , postcode
     , display_county 
  FROM jobs_locations 
GROUP 
    BY postcode

Both sql queries produce this:

job_id display_name postcode display_county
1 1 CB1 Essex
1 1 CM14 Essex
1 1 IG7 Essex
1 1 SE2 London

could you run this please

SELECT postcode
     , job_id
     , COUNT(*) as rows
  FROM jobs_locations 
GROUP 
    BY postcode
     , job_id

It gives this result:

postcode job_id rows
CB1 1 1
CM14 1 1
IG7 1 1
SE2 1 1

there is only one job ???

Yes there is only one job but it has multiple locations which I am trying to get a count of from the jobs_locations table.

Any ideas?

yes

you have only one job per postcode

:slight_smile:

But a job can have multiple postcodes - what’s the answer? :slight_smile:

your query conts the number of jobs per postcode

what you just said, that a job can belong to more than one postcode, doesn’t apply here

it would if you were counting postcodes per job, but you aren’t, you’re counting jobs per postcode, and for that, the answer is 1 each

:slight_smile:

I’m confused. What I am looking to do is fetch a list of jobs, along with a count of unique postcodes for each job returned. Is this not possible? Am I not on the right track :frowning:

yea, that’s possible, so you’ll have to change your subquery from what it does now (counting jobs per postcode) to what you actually want (counting postcodes per job)

the hint for this is that the GROUP BY in the subquery has to be on job_id, not on postcode