Mysql – Join second table based on third table with conditions

join;MySQLorm

I"m using Doctrine for a website that has 3 tables to connect together. In simplest terms, they look like this

Contractor
- id
- name

ContractorJob
- id
- contractor_id
- job_id
- status

Job
- id
- start_date
- end_date

I want to join Jobs to Contractors based on a date range, but you can see that the only way to do that is through the ContractorJob table. It means that a simple inner join between Contractor and ContractorJob is essentially random, and therefore the subsequent join between ContractorJob and Job is mostly useless.

How can I join Contractor to Job using start_date and end_date as conditions?

Pseudo query is as follows

select from contractor c
left join ContractorJob cj on cj.contractor_id = c.id
left join job j with j.contractor_job = cj.id and j.start_date < :start_date and j.end_date > :end_date
where j.id is null

(find all contractors who do not have a job in the given date range)

*note: Doctrine is a web framework DB manager that handles low level query processing so that applications can use more high-level functions. It allows you to say query.innerJoin('a.second_table', 'b') and have the correct join made

Best Answer

There are several problems with the query

  • The select list is missing.
  • The join syntax needs ON, not WITH: a JOIN b ON <some condition>
  • There is a missing on condition (what you correctly identified as arbitrary/random join between Contractor and ContractorJob).
  • There are column names mismatches between the code and the design: (Job.contractor_job vs. Job.id). Which is the correct one?

How to write it with LEFT JOIN / IS NULL. The parentheses are redundant, placed only for making the joins more clear:

select 
    c.*
from 
        Contractor as c
    left join
        ( 
            ContractorJob as cj
        inner join 
            Job as j 
        on  
                j.id = cj.job_id 
            and j.start_date < :start_date 
            and j.end_date > :end_date
        )
    on 
        c.id = cj.contractor_id
where 
    cj.contractor_id is null ;

How to write it with NOT EXISTS which I find easier to grasp:

select c.*
from Contractor as c
where not exists
    ( select * 
      from ContractorJob as cj
        inner join Job as j 
        on  j.id = cj.job_id 
      where j.start_date < :start_date 
        and j.end_date > :end_date
        and c.id = cj.contractor_id
    ) ;
Related Question