Join second table based on third table with conditions
There are several problems with the query


The select list is missing.
The join syntax needs ON, not WITH:  a JOIN b ON &lt;some condition&gt; 
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 &lt; :start_date 
            and j.end_date &gt; :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 &lt; :start_date 
        and j.end_date &gt; :end_date
        and c.id = cj.contractor_id
    ) ;