CODEWITHSUNDEEP
mysqlsql
MerijnM
MerijnM

Reputation: 3

How To Select Where Foreign Key Doesn't Exist in Other Column and Compare Dates From Tables That Aren't Directly Linked

EER-DIAGRAM DATABASE: https://i.sstatic.net/5v87y.jpg

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:

I have written a lot of queries but none of them have managed to cover the use case. A (non-working) example would be:

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

Upvotes: 0

Views: 29

Answers (1)

KL_
KL_

Reputation: 1513

To make it better, try to break your conditions in little pieces:

  • SELECT courseExecution.courseExecutionID, courseExecution.beginDate, courseExecution.endDate, course.title, course.price, co.city WHERE:

    SELECT c.courseExecutionID, c.beginDate, c.endDate, co.title, co.price, co.title

  • 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.

    c.courseExecutionID NOT IN (SELECT courseExecutionID FROM participant)

  • The CourseExecution must not be done, pretty simple just check whether the done column is 0.

    c.done = 0

  • availableDates.beginDate is smaller (<) than courseExecution.beginDate

    a.beginDate < c.beginDate

  • availableDates.endDate is greater (>) than courseExecution.endDAte

    a.endDate > c.endDate

Adding your Joins, it should be like this:

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
WHERE c.courseExecutionID NOT IN (SELECT courseExecutionID FROM participant)
AND c.done = 0
AND a.beginDate < c.beginDate
AND a.endDate > c.endDate

Upvotes: 1

Related Questions