[b]Запрос: [/b]

select t1.rname, t1.matname,
sum(pr.prihod), sum(rs.rashod), sum(pr.prihod-rs.rashod)

from

((select inv.invcode as invcode1, r.rname as rname, m.matname as matname
from rooms r left join invoice inv on r.rid = inv.rid
left join items ite on inv.invcode = ite.invcode
left join materials m on ite.matcode = m.matcode) t1

left join

(select inv.invcode as invcode2, sum(itemsum) as prihod
from invoice inv left join items ite on inv.invcode=ite.invcode
where INVTYPE = 0
group by inv.invcode) pr

on t1.invcode1 = pr.invcode2

left join

(select inv.invcode as invcode3, sum(itemsum) as rashod
from invoice inv left join items ite on inv.invcode=ite.invcode
where INVTYPE = 1
group by inv.invcode) rs

on t1.invcode1 = rs.invcode3)

group by t1.rname, t1.matname
order by 1, 2

Результат:

rname matname sum(pr.prihod) sum(rs.rashod) sum(pr.prihod-rs.rashod)
-------------- --------------- ---------------- ---------------- ----------------
Кабинет №1 Материал №1 150000.........………...null.........………...null
Кабинет №1 Материал №2 null......…......………...630000.........………...null
Кабинет №2 Материал №2 230000............……..null.........………...null
Кабинет №2 Материал №5 null..................…….730000.........………...null
Кабинет №3 Материал №3 null.........……………...320000.........………...null
Кабинет №4 Материал №4 430000............……..null.........………...null
Кабинет №5 Материал №5 null..................…….530000.........………...null

Почему не считает sum(pr.prihod-rs.rashod)?

orawish, спасибо!!!