MySQL联接查询的问题

[poor365]

有如下三个表，要求统计每天每类商品的进货总量，没有进货的类也列出来，列为NULL
1. 商品分类表：DEPARTMENT 主键：INDEXKEY
+---------+----------+
| DEPT_NO | INDEXKEY |
+---------+----------+
| 10 | 10 |
| 20 | 20 |
| 30 | 30 |
| 40 | 40 |
| 50 | 50 |
+---------+----------+
5 rows in set (0.00 sec)

2.商品信息表：INVENTORY 主键:ITEM
+------+-----------+
| ITEM | DEPT_NO |
+------+-----------+
| NO1 | 10 |
| NO2 | 20 |
| NO3 | 30 |
| NO4 | 20 |
| NO5 | 10 |
| NO6 | 40 |
| NO7 | 10 |
+------+-----------+
7 rows in set (0.00 sec)

3.进货清单表： PO_DETAILS 主键：INDEXKEY
+------+-----+-----------+----------+
| ITEM | QTY | ORDERDATE | INDEXKEY |
+------+-----+-----------+----------+
| NO1 | 5 | 0801 | 101 |
| NO1 | 3 | 0802 | 102 |
| NO2 | 6 | 0801 | 103 |
| NO2 | 4 | 0802 | 104 |
| NO3 | 9 | 0801 | 105 |
| NO4 | 5 | 0801 | 106 |
| NO5 | 5 | 0802 | 107 |
+------+-----+-----------+----------+
7 rows in set (0.01 sec)

统计结果应该为：
+-----------+---------+-------+
| ORDERDATE | DEPT_NO | QTY |
+-----------+---------+-------+
| 0801 | 10 | 5 |
| 0802 | 10 | 8 |
| 0801 | 20 | 11 |
| 0802 | 20 | 4 |
| 0801 | 30 | 9 |
| NULL | 40 | NULL |
| NULL | 50 | NULL |
+-----------+---------+-------+
8 rows in set (0.00 sec)
请问如果采用联接join来写查询语句，应该怎么写才能获得正确的统计结果？
我是菜鸟啊，大家帮帮我吧！

[mschen]

-- have a try.

mysql>select b.ORDERDATE,a.DEPT_NO,sum(c.QTY) as QTY
from DEPARTMENT a left outer join INVENTORY b
on a.DEPT_NO=b.DEPT_NO
left outer join PO_DETAILS c
on b.ITEM=c.ITEM
group by b.ORDERDATE,a.DEPT_NO;