根据日期从表中获取事件

Question

I have 3 tables, user, courses and usertocourse

In user table, the fields are given below

| id         | name       |   email   | 

courses table

| course_id | name  |description| enrollment_key  |

enrollment_key is 1 for self enrolled courses, enrollment_key is 0 for admin assigned courses.

usertocourse

| user_id    | course_id  | assigndate | completiondate | started |

when a admin assign a course to user, it will come to this table, or if the user self enrolled also it will come to this table. Started bit is set to 1 only when the user started the courses, else its 0. For self assigned course the completion date is automatically set to some default date say(2000-11-30)

Now what I need is two things

1. I need to get the list of all courses to that user_id from usertocourse table(both self enrolled and assigned courses), and the list of all self enrolled courses from course table except the once which the user is already enrolled.

   SELECT a.*, b.*
   FROM courses a 
   JOIN users_to_courses b
   WHERE a.course_id=b.course_id
   and b.user_id='$user_id'

   The above query returns all the courses, but it does nt check whether it is already assigned or not. I need a query which can check both together.

2. I have a date with me say '2016-07-01', I want to display all the courses which of that month. The below given query works fine if assign date and completion date are in same month, say if the course is starting on 2016-06-28 and completiondate is 2016-07-05, the query will not work as am comparing the $date with assign date.

   SELECT a.*,b.*
   FROM courses a
   JOIN users_to_courses b
   WHERE EXTRACT(YEAR_MONTH FROM b.`assign_date`) = EXTRACT(YEAR_MONTH FROM '$date')
   AND b.`user_id` = '$user_id'
   and a.enrollment_key='0'
   and b.completion_date!='2000-11-30'
   and a.course_id = b.course_id