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
