mysql joins tables creating missing dates

Not sure how to do this correct. I have messed with JOINS all morning. All I can get the is the date range but the dates just repeat over the dates that should be blank.

开发者_如何学C

This is a basic query

SELECT `time`,`protocal`,(`octet`/1024) as `octet10243`,`percent`,`div`,FROM_UNIXTIME(`time`, '%Y-%m-%d') as `newtime3` FROM ipport
WHERE FROM_UNIXTIME(`time`, '%Y-%m-%d') >= '2011-01-05' AND FROM_UNIXTIME(`time`, '%Y-%m-%d') <= '2011-01-08' AND (`protocal` = 'Echo' ) AND `div` = 'XDIV'

This is the result.

"time";"protocal";"octet10243";"percent";"div";"newtime3"
"1290923100";"Echo";"92844.07421875";"1.04435";"XDIV";"2011-01-06"
"1291009500";"Echo";"95110.106445312";"1.0796";"XDIV";"2011-01-07"

I have another table with just date from 2011-01-01 to 2011-01-17. I was using this to join.

This is the result I am looking for.

"time";"protocal";"octet10243";"percent";"div";"newtime3"
"0";"Echo";"0";"0";"XDIV";"2011-01-05"
"1290923100";"Echo";"92844.07421875";"1.04435";"XDIV";"2011-01-06"
"1291009500";"Echo";"95110.106445312";"1.0796";"XDIV";"2011-01-07"
"0";"Echo";"0";"0";"XDIV";"2011-11-08"

Here is my hoarded attempt at JOINing...

SELECT makeupdate.date, FROM_UNIXTIME(ipport.time, '%Y-%m-%d'),`time`,`protocal`,(`octet`/1024) as `octet10243`,`percent`,`div`,FROM_UNIXTIME(`time`, '%Y-%m-%d')
as `newtime3` FROM ipport  JOIN makeupdate ON FROM_UNIXTIME(ipport.time, '%Y-%m-%d') >= '2011-01-05'
AND FROM_UNIXTIME(ipport.time, '%Y-%m-%d') <= '2011-01-08' AND (`protocal` = 'Echo' ) AND `div` = 'XDIV'

Thanks for the help

---

Start with your table of dates and LEFT JOIN to your ipport table, so that you get all rows from the date table combined with matching rows in the ipport table.

SELECT m.date, FROM_UNIXTIME(i.time, '%Y-%m-%d'),`time`,`protocal`,(`octet`/1024) as `octet10243`,`percent`,`div`,FROM_UNIXTIME(`time`, '%Y-%m-%d') as `newtime3` 
    FROM makeupdate m
        LEFT JOIN ipport i
            on m.date = FROM_UNIXTIME(ipport.time, '%Y-%m-%d')
                AND (`protocal` = 'Echo' ) 
                AND `div` = 'XDIV'
    WHERE m.date >= '2011-01-05' AND m.date <= '2011-01-08'