Mysql join two table based on between dates

Question

I have a student attendance view in mysql database like below

id    date         grade
239   01/09/2012   1
239   02/09/2012   0
239   04/09/2012   0
239   05/09/2012   1
239   07/09/2012   0
239   25/09/2012   0
239   26/09/2012   0

And i also have a leave application table in database like below. where approved = 1 and rejected = 0.

id  uid    from            to           status
 1   239    04/09/2012    07/09/2012  approved
 2   239    26/09/2012    26/09/2012  rejected

Now i want to create a query to display approved leave and final grade of student like below

id    date       grade    leave   Fgrade
239   01/09/2012   1                 1
239   02/09/2012   0                 0
239   04/09/2012   0       1         1
239   05/09/2012   1       1         1    
239   07/09/2012   0       1         1
239   25/09/2012   0                 0
239   26/09/2012   0                 0

Please tell me how can i get this result.

Answer 1

Modified

(modifications based on clarifications provided in later comments:)

SELECT a.id
     , a.date
     , a.grade
     , NULLIF(MAX(l.id) IS NOT NULL,0) AS leave
     , IF(MAX(l.id) IS NULL,a.grade,1) AS fgrade
  FROM student_attendance a
  LEFT
  JOIN leave_application l 
    ON l.uid = a.id
   AND l.from <= a.date
   AND l.to + INTERVAL 1 DAY > a.date
   AND l.status = 'Approved'
 GROUP BY a.id, a.date, a.grade

For performance, you likely want indexes

... ON `student_attendance` (`id`, `date`, `grade`) 
... ON `leave_application` (`uid`, `status`, `from`, `to`, `uid`)

You can use EXPLAIN SELECT ... to get information about the access plan.

8.8.1 Optimizing Queries with Explain http://dev.mysql.com/doc/refman/5.5/en/using-explain.html

---

Earlier:

I think this returns the specified result set.

SELECT a.id
     , a.date
     , a.grade
     , l.id AS leave
     , IF(l.id IS NULL,a.grade,1) AS fgrade
  FROM student_attendance a
  LEFT
  JOIN leave_application l 
    ON l.uid = a.id
   AND l.from <= a.date
   AND l.to >= a.date
   AND l.status = 1 /* approved */

The JOIN predicate matches on student id, and a range check of the attendance date within a leave period, as well as the leave being approved.

If there is no matching (overlapping) leave row, then fgrade is assigned the value from the grade column. Otherwise, we're assigning a 1 for fgrade.

Based on the comment left on your question, that literal 1 as the third argument in the IF function could be replaced with a reference to the status column from leave_application. Adding grade and leave status could result in a higher value than you want, if a student was granted leave, but also received a grade. 1+1=2.

IF(l.id IS NULL,a.grade,l.status) AS fgrade

There's a potential that a row from student_attendance will match more than one row from the leave_application table. We can address that with a GROUP BY and an aggregate...

SELECT a.id
     , a.date
     , a.grade
     , MAX(l.id) AS leave
     , IF(MAX(l.id) IS NULL,a.grade,1) AS fgrade
  FROM student_attendance a
  LEFT
  JOIN leave_application l 
    ON l.uid = a.id
   AND l.from <= a.date
   AND l.to >= a.date
   AND l.status = 1 /* approved */
 GROUP BY a.id, a.date, a.grade

If status is actually a character string containing 'Approved', the query can be adjusted. That value for the leave column may not be the id. It's not possible to tell from the example data, since the value for leave matches both the id, and the value of 1 for status 'Approved'. So, that value could actually be from

 l.status AS leave
 NULLIF(l.id IS NOT NULL,0) AS leave
 IF(l.id IS NOT NULL,1,NULL) AS leave
 (l.id/l.id) AS leave

Any of those expressions will give the result shown in the example data.

---

Again, based on additional information provided in comments to your question...

SELECT a.id
     , a.date
     , a.grade
     , NULLIF(MAX(l.id) IS NOT NULL,0) AS leave
     , IF(MAX(l.id) IS NULL,a.grade,1) AS fgrade
  FROM student_attendance a
  LEFT
  JOIN leave_application l 
    ON l.uid = a.id
   AND l.from <= a.date
   AND l.to >= a.date
   AND l.status = 'Approved'
 GROUP BY a.id, a.date, a.grade

Answer 2

Try

SELECT a.id, a.date, a.grade,
       CASE WHEN l.status = 'approved' THEN 1 END `leave`,
       CASE WHEN l.status = 'approved' THEN 1 ELSE a.grade END fgrade
  FROM attendance a LEFT JOIN `leave` l
    ON a.id = l.uid 
   AND a.date BETWEEN l.from AND l.to

Output:

|  ID |       DATE | GRADE |  LEAVE | FGRADE |
----------------------------------------------
| 239 | 2012-09-01 |     1 | (null) |      1 |
| 239 | 2012-09-02 |     0 | (null) |      0 |
| 239 | 2012-09-04 |     0 |      1 |      1 |
| 239 | 2012-09-05 |     1 |      1 |      1 |
| 239 | 2012-09-07 |     0 |      1 |      1 |
| 239 | 2012-09-25 |     0 | (null) |      0 |
| 239 | 2012-09-26 |     0 | (null) |      0 |

Here is SQLFiddle demo