Joins and unions - help!

[subwayman]

I've been trying to work this out for myself but can't quite get it...

I have a table holding daily sales figures:

Date - DATE
StoreID - INTEGER
Sales - DOUBLE

I want to creatre query that will list store sales figures all stores for each date with a row containing (assuming for simplicity two stores):

Date 20051001
Store1 1234.87
Store2 5678.55

Simple,eh? Yes but I also have stores opening and closing on diferent days so store 1 may not have any sales and a particular date (and therefore no record in the sales table). I still need this returning as a zero entry in the store column for that day, e.g.:

Date 20051002
Store1 0.00
Store2 12365.19

I think I need to create a temp table of dates then left join to it. So far I have then created a union of two queries, one for each store. But there must be a better way than this - and what happens when I open another 10 stores!

May be there is a better way to store the sales data in the first place?

Please make my headache go away.....

Thanks in advance.

[tomhath]

Why do you want all stores in one row? This would be a simple LEFT JOIN if you returned a row for each store. If necessary you can rearrange the results in PHP.