I need to do a query and join with all days of the year but in my db there isn't a calendar table.
After google-ing I found
and so on ..
This is how I do it. It creates a range of dates from 2011-01-01 to 2011-12-31:
select date_format( adddate('2011-1-1', @num:=@num+1), '%Y-%m-%d' ) date from any_table, (select @num:=-1) num limit 365 -- use limit 366 for leap years if you're putting this in production
The only requirement is that the number of rows in any_table should be greater or equal to the size of the needed range (>= 365 rows in this example). You will most likely use this as a subquery of your whole query, so in your case any_table can be one of the tables you use in that query.