title: How To Select Where Foreign Key Doesn't Exist in Other Column and Compare Dates From Tables That Aren't Directly Linked
body: <p>EER-DIAGRAM DATABASE: <a href="https://i.stack.imgur.com/5v87y.jpg" rel="nofollow noreferrer">https://i.stack.imgur.com/5v87y.jpg</a></p>
  
  <p>So how this works is that I have a php application where a user can give his available dates (see table: availableDates) for a certain course. Based on that preference the system will give him course executions information (see table: courseExecutions) that meet his criteria for which he can then sign up to, meaning that the SQL-query must have the following requirements: </p>
  
  <ul>
  <li><p>SELECT courseExecution.courseExecutionID, courseExecution.beginDate, courseExecution.endDate, course.title, course.price, co.city <strong>WHERE</strong>:</p></li>
  <li><p>A person has not yet signed up for this execution, this gets checked in the participant table, if courseExecutionID is not linked to a participantID in the participant table then he has not signed up yet.</p></li>
  <li>The CourseExecution must not be done, pretty simple just check whether the done column is 0. </li>
  <li>availableDates.beginDate is smaller (&lt;) than courseExecution.beginDate</li>
  <li>availableDates.endDate is greater (>) than courseExecution.endDAte</li>
  </ul>
  
  <p>I have written a lot of queries but none of them have managed to cover the use case. A (non-working) example would be:</p>
  
  <pre><code>SELECT c.courseExecutionID, c.beginDate, c.endDate, co.title, co.price, co.title
  FROM courseExecution c
  JOIN course co
  ON c.courseID = co.courseID
  JOIN availableDates a
  ON co.courseID = a.courseID
  JOIN person p 
  ON a.personID = p.personID
  WHERE NOT EXISTS (SELECT personID FROM participant WHERE p.personID = participant.personID)
  AND c.done = 0
  ORDER BY courseExecutionID
  </code></pre>