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

- id
- name

- id
- contractor_id
- job_id
- status

- 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

  • 1
    with? The syntax needs on: a join b on <condition> Apr 7 '16 at 14:02
  • Doctrine uses WITH when doing leftJoins, but you're right otherwise Apr 7 '16 at 14:09
  • 1
    What is Doctrine? You have tagged the question with MySQL, not Doctrine. Apr 7 '16 at 14:10
  • My mistake, there is no Doctrine tag. It's a web framework that handles queries directly so you can build requests with a cleaner interface. Apr 7 '16 at 14:17

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:

        Contractor as c
    left join
            ContractorJob as cj
        inner join 
            Job as j 
                j.id = cj.job_id 
            and j.start_date < :start_date 
            and j.end_date > :end_date
        c.id = cj.contractor_id
    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
    ) ;
  • Thank you. Your suggestion about not exists and subqueries pointed me in the right direction. Apr 8 '16 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.