SQL Fetch rows if it satisfies all criteria

Question

I have here a table for schedule of 2 employees. How will I retrieve the rows of a certain employee which has a complete schedule from monday to sunday and starts from 2016-08-15 upto 2016-08-21

| id | emp_id |    date    | day |
|----|--------|------------|-----|
| 1  |  emp1  | 2016-08-17 | Wed |
| 2  |  emp1  | 2016-08-18 | Thu |
| 3  |  emp1  | 2016-08-19 | Fri |
| 4  |  emp1  | 2016-08-20 | Sat |
| 5  |  emp1  | 2016-08-21 | Sun |
| 6  |  emp2  | 2016-08-15 | Mon |
| 7  |  emp2  | 2016-08-16 | Tue |
| 8  |  emp2  | 2016-08-17 | Wed |
| 9  |  emp2  | 2016-08-18 | Thu |
| 10 |  emp2  | 2016-08-19 | Fri |
| 11 |  emp2  | 2016-08-20 | Sat |
| 12 |  emp2  | 2016-08-21 | Sun |

So basically it should retrieve only the entries of emp 2. This is how the output should be

| 6  |  emp2  | 2016-08-15 | Mon |
| 7  |  emp2  | 2016-08-16 | Tue |
| 8  |  emp2  | 2016-08-17 | Wed |
| 9  |  emp2  | 2016-08-18 | Thu |
| 10 |  emp2  | 2016-08-19 | Fri |
| 11 |  emp2  | 2016-08-20 | Sat |
| 12 |  emp2  | 2016-08-21 | Sun |

But if emp1 has monday and tuesday sched it should also be retrieved.

UPDATED

Actually I'm applying this in codeigniter and with the help of the answers I've managed to translate it into a codeigniter active record syntax and this is how my code looks like:

$schedule = $this->db->query("SELECT *
                FROM schedule
                LEFT JOIN employees
                ON employees.user_id = schedule.user_id;
                WHERE schedule.user_id IN (SELECT user_id
                                   FROM schedule
                                   WHERE start >= '2016-08-15' and start <= '2016-08-21'
                                   group by user_id
                                   having count(*) = 7
                                  ); ");


if($schedule->num_rows() > 0) {
    return $schedule->result_array();
    } else {
        return false;
    }

By the way I also have another table that needs to be joined with the schedule table. But this seems to be in an incorrect way because I got an error. How should I resolve this?

Answer 1

Having a full schedule from 2016-08-15 to 2016-08-21 means having seven days scheduled. So you could just check the number of days scheduled in this timeframe and make sure you have seven (different) ones:

SELECT *
FROM   schedule
WHERE  `date` >= '2016-08-15' AND 
       `date` <= '2016-08-21' AND
       emp_id IN (SELECT   emp_id
                  FROM     schedule
                  WHERE    `date` >= '2016-08-15' AND `date` <= '2016-08-21'
                  GROUP BY emp_id
                  HAVING   COUNT(DISTINCT `date) = 7)