sqlite - Generating attendance list for hours without a matching row -

I have a project that calculates working hours from the presence log, which I import from the presence machine. I use the SQLite database and VB.NET.

First I will show the table I use:

  create table [check los] (without [IDCheckLog] Basic primary key operations, [IDEmployee] text not null, [dates] text tap, [in] text, [out] text, [overtime] NUMERIC DEFAULT 0); Making table integer (INTEGER tap primary key); INSERT integer (i) value (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);  

The table check lodge is the data that I import from the presence machine. Overtime columns are calculated in my program. The table integer is used to create the date list, I found it.

I want to create a visual person that shows employee attendance between 2 dates and displays checkload data if the employee is present and is absent if absent. Because there is no data from that employee in the table check lodge, when the employee is absent.

This is the view that I want (this employee is between 10001 2014-10-01 and 2014-10-05):

  Dates | IDE employee | In Out --------------------------------------- 2014-10-01 | 10001 | 07:00 | 16:00 2014-10-02 | 10001 | 07:01 | 15:58 2014-10-03 | 10001 | Empty | Blank 2014-10-04 | 10001 | 07:08 | 15:48, 2014-10-05 | 10001 | Empty | Null  

and this is the query that I have:

  SELECT X. [dates], c . [IDE staff], c. [IN], c. [Out] Form of integer as integer from integer as FOL (Select Date ('2014-10-01', '+' || (Hi * 100 + Ti * 10 + U) || 'day') In crossing the integer as T, the integers add as integers in the form of a date ('2005-01-25', '+' || (Hi * 100 + Ti * 10 + U) || ' Day ') & lt; =' 2014-10-05 ') AS X, Checklogs ASC USING (Dates) Where [IDEmployee] = '10001'  

I got this result from this query:

  dates | IDE employee | In Out --------------------------------------- 2014-10-01 | 10001 | 07:00 | 16:00 2014-10-02 | 10001 | 07:01 | 15:58 2014-10-04 | 10001 | 07:08 | 15:48  

To get zero values ​​for rows without any matches , You need one. And you have to keep in mind that do not filter those rows with a WHERE clause which will not match zero values; To get the dates, which does not match any condition, you must enter the that condition to be included in the condition:

  SELECT ... from (...) Cadets = X.Dates and CADMPF = '10001'  

in the form of