mysql query for a situation?

Question

I have two tables,

OrderTable(orderid,ordersum)

and OrderPayments(orderid,paidamount).

OrderTable

orderid ordersum

ORD123456 40,000.00

ORD789987 30,000.00

OrderPayments

orderid paidamount

ORD123456 10,000.00

ORD123456 20,000.00

ORD123456 10,000.00

ORD789987 28,000.00

For an order , payment can be made in installments.

I have to get the output as

orderid ordersum paidamount

ORD123456 40,000.00 40,000.00

ORD789987 30,000.00 28,000.00

I have done using view.

create view op1 as select orderid,sum(paidamount) as "pamount" from orderpayments group by orderid;

then i used inner join query b/w ordertable and the view (op1).

I want it to be done in a single query, not to use view / any other table.. Is that possible ? suggest me ?

score 0 · Accepted Answer · answered May 23 '15 at 13:22

0

Use inner query. This is example for join statement

select * from OrderTable
inner join (
    select 
        orderid, sum(paidamount) as "pamount" 
    from orderpayments 
    group by orderid
) as tmp using (orderid)

answered May 23 '15 at 13:22

mr mcwolf

2,574
2
14
27

Thanks for the solution.. It Works. But i didnt understand query control flow, can you explain me in detail ? – Anil kumar Kun May 25 '15 at 14:16
what exactly do not you understand? The query works exactly as you would with a view, but instead view using nested query – mr mcwolf May 25 '15 at 14:35
Your query was fine, i got the output . My question is you didn't use on condition, still it is working. how query executed step by step.. By idea is next if i get requirement i should write like you. – Anil kumar Kun May 26 '15 at 06:27
Well written from the inside out. First make the internal requests (like you create views). Then use those queries as tables, just as use and view. In `MySQL` syntax is slightly cramped. So, perhaps, that you easier to understand with `with` statement http://sqlfiddle.com/#!15/7f924/12 – mr mcwolf May 26 '15 at 08:36
Thanks for the explanation. i got it.. First i tried like in MSSQL CTE, but filed... Later, by ur example i got it.. Thanks. – Anil kumar Kun Jun 05 '15 at 06:01

mysql query for a situation?

1 Answers1