Only Select Friday and coming Monday in mysql

Question

I have assignment on my hand of building an Attendance system which I am doing all right but I am stuck at one place. When counting the leaves, if a person takes a leave on Friday and then take leave again on Monday, then the in between Saturday and Sunday should count as leaves too.

I have been able to extract only the Fridays and Mondays from my table by following query:

SELECT * FROM  `main` WHERE (DAYOFWEEK( DATE ) =2 OR DAYOFWEEK( DATE ) =6 ) 
AND emp_no =4 AND STATUS ='leave' ORDER BY DATE ASC 

but I don't know how to select the Friday and the next Monday only so I know that the person was on leave on Friday as well as Monday.

Any help would be appreciated.

Answer 1

This will give you all "leaves" which span a Friday and the following Monday:

SELECT *
FROM   main fri JOIN main mon
    ON fri.DAYOFWEEK(DATE)=6
   AND mon.DATE = fri.DATE + INTERVAL 3 DAY
   AND fri.emp_no = mon.emp_no
WHERE  fri.STATUS='leave' AND mon.STATUS='leave'