MySQL - subquery in WHERE unknown column in join (count in subquery)

Question

The following query is giving me this error:

Error Code: 1054. Unknown column 'jobs.id' in 'on clause'


SELECT clinics.id as cid, clinics.name, clinics.address AS clinicAddress,
            clinics.post_code as clinicZip, clinics.city AS clinicCity, clinics.region,
            countries.full_name AS country,
            jobs.name AS jobName, jobs.created_at AS jobCreatedAt, jobs.from AS jobStarts,
            jobs.description AS jobDescription, jobs.rates, jobs.max_applicants, jobs.created_at,
            locum_types.name AS locumName,
            job_statuses.name AS statusName,
            CONCAT(users.first_name, ' ', users.last_name) AS userName, users.title AS userTitle,
            roles.name AS roleName            
            FROM clinics
            JOIN countries ON countries.id = clinics.country_id
            JOIN jobs ON clinics.id = jobs.clinics_id
            JOIN users ON users.id = jobs.users_id
            JOIN roles ON roles.id = users.roles_id
            JOIN locum_types ON locum_types.id = jobs.locum_types_id
            JOIN job_statuses ON job_statuses.id = jobs.statuses_id
            WHERE (LOWER(`clinics`.`city`) like '%abbey%'
            OR LOWER(`clinics`.`post_code`) like '%abbey%'
            OR LOWER(`clinics`.`region`) like '%abbey%')
            AND jobs.to >= '2020-01-31 07:53:48'
            AND applicants IN (SELECT COUNT(user_jobs_application.id) as applicants 
                FROM user_jobs_application
                JOIN user_jobs_application as jobApplication ON user_jobs_application.jobs_id = jobs.id
                HAVING applicants < jobs.max_applicants)
            LIMIT 20 OFFSET 0

I am trying to count all jobs in the user_jobs_application table and check if the count is lesser than the max_applicants in jobs table.

The structure of the jobs table is following:

id
max_applicants
created_at

The user_jobs_application table (this is pivot table):

id
users_id
jobs_id

Basically I need to count all applications for the job and check if it within limits (max_applicants). Any idea what am I doing wrong here?

in the inner SQL query, you have "SELECT COUNT(user_jobs_application.id) as applicants FROM user_jobs_application JOIN user_jobs_application" the join part should be "JOIN jobs" — dacuna, Jan 30 '20 at 21:28
You have used "JOIN user_jobs_application as jobApplication" but where is the jobApplication used? — user3115056, Jan 30 '20 at 21:29
As an aside, it seems unlikely that you would be using a case sensitive collation — Strawberry, Jan 30 '20 at 21:34
Might answer your question https://stackoverflow.com/questions/4065985/mysql-unknown-column-in-on-clause — Mahamad A. Kanouté, Jan 30 '20 at 22:35
I'd post an answer if I could get the ERD. In my knowledge, the order of the JOIN's usually creates this error. For example, If I ever have tables A->B->C with no connection between A and C I need to JOIN B to C first before joining A to B in order to have the result from A-B-C. Check for cross JOIN's ! — Mahamad A. Kanouté, Jan 30 '20 at 23:44
I have a few questions. When you do `AND applicants IN ( ..` where does the column `applicants` coming from? And what value does it hold? `id` or total count of `applicants`? If that so, isn't it strange to use `IN`.. because it seems that you wanted find a bunch of `id` instead. — FanoFN, Jan 31 '20 at 01:46
@tcadidot0 I need to count if number of users applied to the job (applicants) is lesser than maximum user allowed for that job (jobs.max_applicants) — Sasha, Jan 31 '20 at 09:38

score 1 · Answer 1 · answered Jan 31 '20 at 00:00

1

Try :

JOIN user_jobs_application as jobApplication ON user_jobs_application.id = jobs.id

Instead of :

JOIN user_jobs_application as jobApplication ON user_jobs_application.jobs_id = jobs.id

answered Jan 31 '20 at 00:00

Mahamad A. Kanouté

172
9

score 1 · Answer 2 · answered Jan 31 '20 at 02:11

The sub-query that you did after applicants IN is being treated separately from the outer query so it doesn't "see" the jobs table as it is out of it's scope.

Why don't you start with a simple query first. Just to get the result you wanted. Focus on the two tables only. You might use a query like below:

SELECT jobs.id, jobs.max_applicants, uja.jobs_id, uja.total_applicants 
FROM   jobs 
JOIN   (SELECT jobs_id,COUNT(*) total_applicants FROM user_jobs_application GROUP BY jobs_id) uja
ON     uja.jobs_id = jobs.id
WHERE  jobs.to >= '2020-01-31 07:53:48'
AND    uja.total_applicants > jobs.max_applicants;

Make the count from user_jobs_application table into a sub-query then join it with jobs table. See if this can return the result you wanted. If it does, then you can implement this into your original query. Probably something like this:

SELECT clinics.id AS cid, clinics.name, clinics.address AS clinicAddress,
            clinics.post_code AS clinicZip, clinics.city AS clinicCity, clinics.region,
            countries.full_name AS country,
            jobs.name AS jobName, jobs.created_at AS jobCreatedAt, jobs.from AS jobStarts,
            jobs.description AS jobDescription, jobs.rates, jobs.max_applicants, jobs.created_at,
            locum_types.name AS locumName,
            job_statuses.name AS statusName,
            CONCAT(users.first_name, ' ', users.last_name) AS userName, users.title AS userTitle,
            roles.name AS roleName            
            FROM clinics
            JOIN countries ON countries.id = clinics.country_id
            JOIN jobs ON clinics.id = jobs.clinics_id
            JOIN users ON users.id = jobs.users_id
            JOIN roles ON roles.id = users.roles_id
            JOIN locum_types ON locum_types.id = jobs.locum_types_id
            JOIN job_statuses ON job_statuses.id = jobs.statuses_id
            /*Insert here*/
            JOIN 
            (SELECT jobs_id,COUNT(*) total_applicants 
             FROM   user_jobs_application GROUP BY jobs_id) uja ON  uja.jobs_id = jobs.id
            /**/      
            WHERE (LOWER(`clinics`.`city`) LIKE '%abbey%'
            OR LOWER(`clinics`.`post_code`) LIKE '%abbey%'
            OR LOWER(`clinics`.`region`) LIKE '%abbey%')
            AND jobs.to >= '2020-01-31 07:53:48'
            /*and add the condition here*/
            AND uja.total_applicants > jobs.max_applicants
            /**/
            LIMIT 20 OFFSET 0;

MySQL - subquery in WHERE unknown column in join (count in subquery)

2 Answers2