SQL Query issue with joining get data that doesn't exist

Asked Feb 09 '16 at 11:42

Active Feb 09 '16 at 11:42

Viewed 57 times

I have date range: Date 1 and Date 2

in one table I have Date that are registered according to user logging in.

when I do SQL between Date1 to Date2, it only gives Date that exist.

so for example, 01-01-2016 to 01-04-2016: four days

I need:

01-01-2016 - 0
01-02-2016 - 5
01-03-2016 - 3
01-04-2016 - 0

instead I get:

01-02-2016 - 5
01-03-2016 - 3

because other two dates do not exist in the table.

how can I solve it?

I need to display all date selected with 0

asked Feb 09 '16 at 11:42

Malarivtan

1

If the data is not present then how it could return those data? Add them manually with code or in query. – Sougata Bose Feb 09 '16 at 11:45
Do you have a time table containing all dates? – sagi Feb 09 '16 at 11:47
1

MySQL is really bad at inventing non existant data. _Bad MySQL_ What we need is _MAGIC MYSQL_ – RiggsFolly Feb 09 '16 at 11:48
@sagi NO I don't have timetable of calendar. – Malarivtan Feb 09 '16 at 11:48
@sagi that makes it too heavy? I mean creating extra table with data every time – Malarivtan Feb 09 '16 at 11:51
No, not every time, make it once for all date since lets say the year of 2000 and the year of 2030. then you will only have to join to it – sagi Feb 09 '16 at 11:52
@sagi hope you are not joking? – Malarivtan Feb 09 '16 at 11:55
Not at all, calender table can be used in a lot of different ways. You can easily find a script to create one on google. – sagi Feb 09 '16 at 11:57
1

Use something like this answer http://stackoverflow.com/questions/2157282/generate-days-from-date-range to generate a date range, then left join each date to your data, replacing any nulls as required. – Liesel Feb 09 '16 at 11:58
show your table structure (i mean create table code) @user4499992 – 1000111 Feb 09 '16 at 14:33
You need to get your DeLorean up to 88 MPH. Then you'll be able to fix the issue. – Dave Kelly Feb 09 '16 at 21:47
@Dave watching too much Back to the future? I think you might be wright\ – Malarivtan Feb 10 '16 at 07:19

0 Answers0