左连接问题

左连接问题

有3张表，A与B是主子表,通过partcode相连接，C表通过cgid与B表左连接，可是A表怎么办呢？
例如：
A表
aid    partcode        status

1        001            70

2        002            10

3        003            50

B表

bid    partcode        cgid        price

1      001              b001        2.2

2      002              b002        3.0

3      003              b003        1.0
C表

cid    cgid          qty   

1      b001          20

2      b002          10

3      b003          30

4      b004          40

想实现的结果为

cgid        price*qty

b001        44(因为status='70')

b002        0 (因为status != '70')

b003        0 (因为status != '70')

b004        0 (因为status != '70')

select b.cgid , b.price * c.qty from a , b, c where a.partcode = b.partcode and b.cgid = c.cgid and a.status = '70'
union all
select b.cgid , 0  from a , b, c where a.partcode = b.partcode and b.cgid = c.cgid and a.status <> '70'

select c.cgid,nvl(b.price*c.qty,0)
from a join b
  on a.partcode=b.partcode and a.status=70
  right join c on b.cgid=c.cgid