Hi,

I have a master/detail setup for invoices! It is a classic invoice
strusture where master table represent a header of invoice, and a
detail table represent items of master table!
So here is a sample of master:
id no date customer
1 10 13.03.06 customerxzy
2 15 14.03.06 customerabc
3 20 15.03.06 customerxxx
and detail sample:
id mid article qty price type
1 1 art123 1 100 X
2 1 art554 1 150 M
3 2 art123 2 100 X
4 3 art556 3 200 X
5 3 artfff 1 150 S
And here is a final structure that I must get with SQL statement:
id no date customer col_a col_b col_c
1 10 13.03.06 customerxyz 100 150 0
2 15 14.03.06 customerabc 200 0 0
3 20 15.03.06 customerxxx 600 0 150

Like you can see, this resultset is contain id, no, date, customer
fields form master table and col_a -> sum(qty*price) where type = X
and col_b -> sum(qty*price) where type = M and col_c -> sum(qty*price)
where type = S!

I try with:
SELECT M.Id, M.No, M.Date, M.Customer,
(SELECT SUM(D.Qty*D.Price) FROM Detail D WHERE D.Mid = M.Id AND D.Type
= 'X') AS Col_a,
(SELECT SUM(D.Qty*D.Price) FROM Detail D WHERE D.Mid = M.Id AND D.Type
= 'M') AS Col_b,
(SELECT SUM(D.Qty*D.Price) FROM Detail D WHERE D.Mid = M.Id AND D.Type
= 'S') AS Col_c
FROM Master M, Detaim D
WHERE D.Mid = F.Id

BUT it is a wrong SQL statement, where im my error!

Thanks for help!