CODEWITHSUNDEEP
mysqlsqljoin
Alex
Alex

Reputation: 581

Mysql left join table not between dates

I have the following issue, I've got 3 tables the first one is called courses where I have 

courses| id | start     | end        |
--------------------------------------
          1  2012-10-12 | 2012-11-12 |

students| id | available_start | available_end | 
-------------------------------------------------
          1     2012-10-13         2012-11-11
          2     2012-11-06         2012-11-08

students_to_courses | student_id | course_id |
-------------------------------------------------
                          1            1

So I'm trying to find which students are available for courses periods. So if the student is added to student_to_courses and dates are between the course dates I don't need it. I've got the feeling that the query should be with a sub query but I really don't understand them. My query now is looking like this but doesn't work properly.

SELECT s.id
FROM (`students` s)
LEFT JOIN `student_to_course` s2c ON `s2c`.`student_id` = `s`.`id`
LEFT JOIN `courses` assigned_2_course ON `s2c`.`course_id` = `assigned_2_course`.`id`
LEFT JOIN `courses` c ON `c`.`id` = 1 
WHERE 

(
(s.available_start NOT BETWEEN assigned_2_course.start AND assigned_2_course.end 
AND
s.aviailable_end NOT BETWEEN assigned_2_course.start AND assigned_2_course.end 
) OR assigned_2_course.end IS NULL)

AND
 `s`.`available_start` BETWEEN c.start AND c.end
AND `s`.`available_end` <= c.end
GROUP BY `s`.`id`
ORDER BY `s`.`id` desc

Here is http://sqlfiddle.com/#!2/49c11/1

now works, but doesn't remove the students which are assigned in other courses with same dates how you can see I'm trying to get available students for course 3 which starts 02-03 and ends 02-08, student 2 is in course 3 so is not shown, student 1 is in course 2 which starts 01-03 and ends 03-01 so shouldn't be available.

Any help will be appreciated.

Upvotes: 0

Views: 203

Answers (1)

AgRizzo
AgRizzo

Reputation: 5271

I used your SQL fiddle (but added another student record) http://sqlfiddle.com/#!2/246645/1

try this to find all students that could attend course 3 because they are not in a class during that time:

SELECT student.*
FROM student
JOIN course
  ON course.id = 3
    AND student.available_start <= course.`start` 
    AND student.available_end >= course.`end`
WHERE NOT EXISTS
  (SELECT *
   FROM student_to_course
   JOIN course AS c
     ON student_to_course.course_id = c.id
    WHERE student.id = student_to_course.student_id
       AND (course.`start` BETWEEN c.`start` AND c.`end`
             OR
            course.`end` BETWEEN c.`start` AND c.`end`
             OR
            c.`start` BETWEEN course.`start` AND course.`end`));

Upvotes: 1

Related Questions