Is this possibly an impossible MySQL query?

What I have so far:

   SELECT jobs.id
     FROM jobs
LEFT JOIN employees_jobs
       ON employees_jobs.job_id = jobs.id
LEFT JOIN line_items
       ON line_items.job_id = employees_jobs.job_id
LEFT JOIN employee_statements
       ON line_items.employee_statement_id = employee_statements.id
    WHERE (
              employee_statements.employee_id != 17 OR
              employee_statements.employee_id IS NULL
          )
      AND employees_jobs.employee_id = 17
 GROUP BY jobs.id
 ORDER BY jobs.delivery_date ASC

• employee_statements have many line_items (foreign key employee_statements_id in the line_items table.).
• jobs have many line_items as well (foreign key job_id in the line_items table).

(line_items is basically a join table for employee_statements and jobs)

• employees_jobs is a join table for a many to many relationship between an employees table and the jobs table.
• employee_statements belong to employees (foreign key employee_id in the employee_statements table)

What I'm trying to do:

I have a scenario where employee statements are created with line items. These line items reference jobs that were completed. Multiple employees can be assigned to each job.

Say we have an employee with id = 17. I want to get all jobs that have not been associated with a line item on any of his previous employee statements yet.

So, all of these would be included in the results:

1. All jobs that employee 17 worked on that are not associated with a line item.
2. All jobs that employee 17 worked on that have been associated with a line item on a different employee's statement.

So if employee 17 and employee 14 both worked on the same job, and one of employee 14's statements already has a line item for that job (but employee 17's statements do not have that line item), the job should show up in my query result.

Where I'm stumped:

The above query returns all jobs that employee 17 worked on, regardless of whether they have been assigned to anyone's line Items.

I understand why.

In the conditional logic, I check if the employee_statements.employee_id IS NULL. This returns jobs that are not on any statements.

I also check for employee_statements.employee_id != 17. This is for jobs that are on other employee's statements.

So, in effect I'm getting all jobs that he worked on in the results, regardless of whether he has had one assigned to himself (because if another employee has it assigned, the second condition grabs it). I can't figure out h开发者_高级运维ow to word this to return just the jobs I want. (it's been a long day. ;))

A long day for me as well, so I may be missing something, but how about this:

SELECT jobs.id
     FROM jobs
INNER JOIN employees_jobs
       ON employees_jobs.job_id = jobs.id
WHERE NOT EXISTS (SELECT 'X' FROM line_items
                   INNER JOIN employee_statements
                     ON line_items.employee_statement_id = employee_statements.id
                     AND employee_statements.employee_id = 17
                   WHERE line_items.job_id = employees_jobs.job_id)
AND employee_jobs.employee_id = 17
GROUP BY jobs.id
ORDER BY jobs.delivery_date ASC

I don't quite get what you want, but shouldn't you start with something like:

(employee_statements.employee_id = 17 AND
/* SOME QUERY HERE TO DETERMINE IF NOT ASSOCIATED */)
OR
(employee_statements.employee_id = 17 AND
/* SOME QUERY HERE TO DETERMINE IF ASSOCIATED WITH OTHER EMPLOYEE STATEMENT */ )

It's hard to tell what exactly is going on, and it's been a long day here too, but you could probably get this now :)

It looks like you would need a second query as this is only returning jobs associated with employee 17 in the first place...