majstoru wrote:

> 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!
>

Why JOIN?
Unrelated to your problem, but in your case a SP will be faster as you
need 3 parses in you current query and as SP will only use one

HTH
Radu