Trouble executing multiple left joins in query?-CodePudding

I have four tables where I am trying to left join the 2nd-4th to the one on the left in this picture. From left to right:

1st table (jobs) is a table of jobs

2nd table (applications_jobs) is a bridge table to link jobs and application IDs

3rd table (applications) is applications

4th table (candidates) is candidates based on those applications

I want to get some columns from 1st table (jobs) and 4th table (candidates). I want to get job name (name) and status (status) columns from jobs table. I want to get first name (first_name) and last name (last_name) from candidates table.

Here's what I've tried:

SELECT
    name, status, first_name, last_name
FROM
    jobs, candidates
left join 
    applications_jobs aj on jobs.job_id = id
left join 
    applications a on aj.job_id = a.id
left join 
    candidates c on a.candidate_id = c.id

but get a error:

ERROR:  invalid reference to FROM-clause entry for table "applications_jobs"

HINT:  There is an entry for table "applications_jobs", but it cannot be referenced 
from this part of the query.

any ideas?

CodePudding user response：

The items you are selecting should be identified by the table they are coming from when you are performing joins, though it is not always necessary if the tables don't share column names. By writing it out, it would help to prevent the confusion you're having with the FROM clause.

The FROM clause can only be from a single table. In this case, it would be your 'jobs' table. Also, to properly reference your columns in your query, the first join should be application_jobs aj ON aj.job_id = jobs.id, and your second join should be applications a ON aj.application_id = a.id.

SELECT
    "jobs".name, "jobs".status, "c".first_name, "c".last_name
FROM
    jobs
left join 
    applications_jobs aj on aj.job_id = jobs.id
left join 
    applications a on aj.application_id = a.id
left join 
    candidates c on a.candidate_id = c.id

If you are still getting NULLs for the first and last names, Then you don't have candidates that have applications for that specific job. If you want to omit results that would otherwise be NULL, you can do an INNER JOIN on candidates so that it only returns records that exist on both sides of the equation.