Select all records in a date range even if no records present

Question

I have a database of employees and their attendance. I have to create the date range reports of attendance.

Attendance Database table

I am using below query

SELECT a.* FROM attendance a WHERE a.user_id=10 AND (a.date BETWEEN '2017-01-06' AND '2017-01-10')

Result:-

I also want records for all given date range but some dates record is not present in database and i want that dates shows null values to corresponds to that dates as shown in below image.

I think there is something else wrong because the query isn't wrong, it just filters the date and the user_id, which are in both cases correct. can you try to remove the `a.` and just do a normal query? (SELECT * FROM attendance WHERE user_id=10 AND (date BETWEEN '2017-01-06' AND '2017-01-10') — Tosfera, Jan 11 '17 at 08:16
One would normally handle issues of data display (such as missing rows) in application code — Strawberry, Jan 11 '17 at 08:17
@Tosfera In the database we don't have records for that dates (07,08) but i want the response including missing date of provided interval that i shown in the above image. — Manoj Kumar, Jan 11 '17 at 09:08
@Strawberry i can write the application code to handle this situation. It would better if we get response that we want rather then writing a code. — Manoj Kumar, Jan 11 '17 at 09:10
So ultimately, you output this to a command line window? :-( — Strawberry, Jan 11 '17 at 09:46
Does the end user use a Command Line Interface (or something like PHPMyAdmin) to review and interrogate the data? — Strawberry, Jan 11 '17 at 10:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/132898/discussion-between-manoj-kumar-and-strawberry). — Manoj Kumar, Jan 11 '17 at 10:38

score 0 · Answer 1 · edited May 23 '17 at 12:10

Try this. I am not able to run it but the idea is to generate a date range based on this answer.

Use this date range as derived table d.
Do a d left join attendance adding your condition so you will get all the columns for matching data and null for not matching data.

This will give you rest of the data, except employee id. I suggest you can hardcode it in select query.

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-06' AND '2017-01-10' 
    ) d
left join 
    attendance a
on d.date = a.date
where 
a.user_id=10

i executed in the database this will not work... i have already googled about it and visited tried all the solution that i found but no single one worked for me.. — Manoj Kumar, Jan 11 '17 at 09:12
Why will this not work? Will u get any error? It would be easier if you used sqlfiddle.com and create sample tables so one can try running the queries. — Utsav, Jan 11 '17 at 09:21

Select all records in a date range even if no records present

1 Answers1