MySQL: Finding the missing values from tables

Question

I have two different tables:

checkin_out consists of two fields: emp_code, checked_date
temp_days consists of two fields: id, date_value

Table checkin_out has following data:

emp_code | checked_date
-----------------------
001        2012-11-01
001        2012-11-02
001        2012-11-03
002        2012-11-01
003        2012-11-01
003        2012-11-02

While table temp_days has following data:

id  | date_value
-----------------
1     2012-11-01
2     2012-11-02
3     2012-11-03
4     2012-11-04
5     2012-11-05

From the above tables, I need to show the missing dates in the table temp_days; I need to query to get a result as follow:

emp_code  |  date_value
-----------------------
001          2012-11-04
001          2012-11-05
002          2012-11-02
002          2012-11-03
002          2012-11-04
002          2012-11-05
003          2012-11-03
003          2012-11-04
003          2012-11-05

If anyone could help, please! Thanks!

Will A · Accepted Answer · 2012-11-30 13:39:04Z

1

The below works for a more complex data set with multiple emp_codes.

SELECT  alldays.emp_code, alldays.date_value
FROM    (
          SELECT date_value, emp_code
          FROM temp_days
          CROSS JOIN checkin_out
          GROUP BY date_value, emp_code
        ) alldays
        LEFT JOIN checkin_out C
            ON alldays.date_value = C.checked_date
            AND alldays.emp_code = C.emp_code
WHERE   C.emp_code IS NULL

answered Nov 30, 2012 at 13:39

Will A

25k5 gold badges54 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thavarith Over a year ago

Thanks for your solution, let me try it first.

Thavarith Over a year ago

You're like a genius in SQL. It works perfectly for more complex data with multiple emp_codes. Thank you very much indeed for the brilliant solution. I'd spent my whole day looking for the right solution, only this one works for me.

Will A Over a year ago

No problem - CROSS JOIN is ideal for this kind of query.

John Woo · Accepted Answer · 2012-11-30 13:12:47Z

0

SELECT  c.emp_code, a.date_value
FROM    temp_days a
        LEFT JOIN checkin_out b
            ON a.date_value = b.checked_date
        CROSS JOIN 
        (
            SELECT emp_code
            FROM checkin_out
            GROUP BY emp_code
        ) c
WHERE   b.emp_code IS NULL

SQLFiddle Demo

answered Nov 30, 2012 at 13:12

John Woo

265k70 gold badges509 silver badges500 bronze badges

5 Comments

Will A Over a year ago

This'll work for the simple example - but the CROSS JOIN is not right as there's nothing joining it to "those days which are not present for a given emp_code".

Thavarith Over a year ago

@WillA I don't really get what you mean on the point that said "CROSS JOIN is not right as there's nothing joining it to <<those days which as not present for a given emp_code>>". It would be great if you could raise any example that make the CROSS JOIN not right as you mentioned above. Thanks

Will A Over a year ago

Try adding {emp_code = 002, checked_date = 2012-11-01} to your checkin_out table, then run the query.

Thavarith Over a year ago

@Kuya-John I have tested with my real data by applying your solution and I'm wondering why it took up to 9.2880 sec to get result from my localhost server with 99 rows found.

Thavarith Over a year ago

@WillA Yes, you're right about it. I didn't realize until I tested and looked thru all employees in my real data and see that no matter what I still get the same result for all employees. I could see your point here now. Thanks a lot. And It seems that the this solution only works for a single employee.

Collectives™ on Stack Overflow

MySQL: Finding the missing values from tables

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related