MYSQL UNION AND ADD

[tomtimms]

Ok I have been trying to figure this out for awhile and can't seem to grasp the answer.  I have 2 tables. 

 

Table 1 Has (Date,Amount) and table 2 has (Date,Amount).  I need to join these tables together on the date field.  Table 1 has values of

 

2010-07-03 01:00:00 / $500

2010-07-03 03:00:00 / $200

2010-07-03 04:00:00 / $100

 

Table 2 is just an hour table that has the following.

 

00:00:00 / $0

01:00:00 / $0

02:00:00/ $0

etc.etc.etc

 

I need to union all the tables together so the end result will show.

00:00:00 / $0

01:00:00 / $500

02:00:00 / $0

03:00:00 / $200

04:00:00 / $100

 

I tried doing a left join however I can't get missing vales from table 2.

 

Current Join Code, Maybe a Union?

 

SELECT date_format(rph.date,'%H:00:00') AS date,SUM(rph.amount + rh.amount) AS amount FROM table2 rh LEFT JOIN table 1 rph ON date_format(rh.date,'%H:00:00') = date_format(rph.date,'%H:00:00') WHERE rph.date BETWEEN '2010-07-03 00:00:00' AND '2010-07-03 23:59:59' GROUP BY date_format(rph.date,'%H:00:00') 

 

 

[fenway]

Yes, why not UNION?