0

I have a problem, where Google and 15 years in IT don't help me anymore and I ran, finding the solution, into a corner, I hope some can help me out of.

I have a table with person data, a table with person_assignments and a table with projects. Each person can have been in multiple projects (which is connected via person_assignments).

Now I want to get every person, who do not have a current project. I know how to get only persons, who currently have a project. And I know how to get only person_assignments which currently have no project.

But the following statement only delivers persons, which had no project in the past, not knowing, that they might have a current project:

SELECT DISTINCT e.id, e.pronoun, e.first_name, e.last_name, e.cost_center_id, e.roles_id, e.e_mail, e.phone, e.office_id, e.work_percentage
FROM Employees e 
LEFT OUTER JOIN Employee_Assignments ea ON ea.employee_id = e.id
WHERE ((NOT((ea.since_date <= DATE_ADD(NOW(), INTERVAL +2 WEEK) OR ea.since_date IS NULL)AND(NOT(ea.until_date <= DATE_ADD(NOW(), INTERVAL +2 WEEK)) OR ea.until_date IS NULL))OR NOT ea.booking_type = "hard") OR NOT {{ switch.value }})

I was thinking about getting all persons, who had no project and then substract them from the persons, who currently have a project, but I can't find a statement to substract to sets from another in sql, since MINUS doesn't work and I have a quite a few WHERE Clauses to take care of, which I stripped here for simplicity.

Attached the fiddle: http://sqlfiddle.com/#!9/402fc9/1

DonMarco
  • 405
  • 2
  • 14
  • 34
  • 1
    Not diving deep into your question, but there are a few ways to emulate `MINUS` in MySQL. The most well-known ones are an anti-join pattern (https://stackoverflow.com/a/31398185/625594) and using `NOT IN (...)` subquery. – Sergey Kudriavtsev Feb 01 '23 at 09:28
  • Can you add the output expected from the query ? – SelVazi Feb 01 '23 at 09:53
  • You need to apply the filter conditions on the employee_assignments table **before** you perform the join. The where clause applies **after** the join. – Shadow Feb 01 '23 at 10:15
  • 1
    @SergeyKudriavtsev, I reworked my entire statement and worked with ´not in´since I was able, to make the capsuled statement so small, ´not in´doesn't cost me as much, as I had feared. – DonMarco Feb 01 '23 at 11:07

0 Answers0