How to find missing date and shift in MySQL?

Question

There are three columns in my MySQL table:

id   | date       | shift  
-----+------------+---------
52   | 2017-01-01 | 1
51   | 2017-01-01 | 2
51   | 2017-01-01 | 3
51   | 2017-01-02 | 1
51   | 2017-01-02 | 3
51   | 2017-01-03 | 1
51   | 2017-01-03 | 2
51   | 2017-01-05 | 1
51   | 2017-01-05 | 2
51   | 2017-01-05 | 3

In this table two shifts are missing; 2nd shift of 2017-01-02, and 3rd shift of 2017-01-03. and 2017-01-04 date are full missed So how can I find this with a MySQL query? and i want condition that find where id='51' then result is 1st shift is also missing in 2017-01-01 and also find that missed 27 days and show 27 dates. please help me.

Answer 1

A way is by making all acceptable rows then filter current rows like this:

select *
from (
    select `date`
    from `yourTable`
    group by `date`) as `td`     -- gathering unique data
cross join (
    select 1 `shift`
    union all select 2
    union all select 3) as `ts`  -- generating a source for shifts
-- filtering those are not exists in current rows
where not exists (
    select 1 from `yourTable` as `ti`
    where `ti`.`date` = `td`.`date` and `ti`.`shift` = `ts`.`shift`);

MySQL Fiddle Demo

---

Update :

Answer to your question in comments can be this:

select *
from (
  select *
  from (
      select `id`, `date`          -- <= added `id`
      from `yourTable`
      group by `id`, `date`) as `td`     -- gathering unique data <= added `id`
  cross join (
      select 1 `shift`
      union all select 2
      union all select 3) as `ts`  -- generating a source for shifts
  where not exists (
      select 1 from `yourTable` as `ti`
      -- filtering those are not exists in current rows
      where `ti`.`date` = `td`.`date` 
        and `ti`.`id` = `td`.`id`  -- <= added `id` filter
        and `ti`.`shift` = `ts`.`shift`)
  ) as `t`
where `id` = 51;      -- <= now you can filter over its result

MySQL Fiddle Demo

---

Update :
As you changed your question again:

select *
from (
  select *
  from (
      select a.Date 
      from (
          select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
          from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
          cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
          cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
      ) a
      where a.Date between '2017-01-01' and '2017-01-04'
       ) as `td`     -- gathering unique data
  cross join (
      select 1 `shift`
      union all select 2
      union all select 3) as `ts`  -- generating a source for shifts
  where not exists (
      select 1 from `yourTable` as `ti`
      -- filtering those are not exists in current rows
      where `ti`.`date` = `td`.`date` 
        and `ti`.`id` = 51
        and `ti`.`shift` = `ts`.`shift`)
  ) as `t`;

MySQL Fiddle Demo