Optimized Query

I am having a structure of 3 tables

Table mintemp consist of matcode,min_qty,jo_no,mr_no
Table min_out_body consist of matcode,out_qty,jo_no,mr_no
Table eu_min_out_body consist of matcode,out_qty,jo_no,mr_no

And data as follow:

[mintemp]
matcode       min_qty           jo_no           mr_no
xxx           100               1A              A11
xxx           150               2A              A22

[min_out_body]
matcode       out_qty           jo_no           mr_no
xxx           10                1A              A11
xxx           60                1A              A11
xxx           100               2A              A22

[eu_min_out_body]
matcode       out_qty           jo_no           mr_no
xxx           20                1A              A11
xxx           50                2A              A22

What i am trying to achieve is to have a result:

matcode        min_qty          jo_no           mr_no          balance
xxx            100              1A              A11            10
xxx            150              2A              A22            0

so far the query i am using is :

```
SELECT
mintemp.matcode,
mintemp.min_qty,
(mintemp.min_qty-(
select ifnull(sum(out_qty),0)
FROM min_out_body
WHERE matcode=mintemp.matcode
and jo_no=mintemp.jo_no
and mr_no=mintemp.mr_no
)-(
select ifnull(sum(out_qty),0)
FROM eu_min_out_body
WHERE matcode=mintemp.matcode
and jo_no=mintemp.jo_no
and mr_no=mintemp.mr_no
)
) as total
FROM mintemp
WHERE mintemp.matcode = 'xxx'
and (mintemp.min_qty - (select
ifnull(sum(out_qty),0)
FROM min_out_body
WHERE matcode = mintemp.matcode
and jo_no = mintemp.jo_no
and mr_no = mintemp.mr_no) - (select
ifnull(sum(out_qty),0)
FROM eu_min_out_body
WHERE matcode

SELECT
    mintemp.matcode,
    mintemp.min_qty,
    (mintemp.min_qty
        - IFNULL(min_out_body.min_out_body_sum,0)
        - IFNULL(eu_min_out_body.eu_min_out_body_sum,0)
    ) as total
FROM mintemp
LEFT JOIN (
        SELECT  jo_no, mr_no, matcode, SUM(out_qty) AS min_out_body_sum 
        FROM    min_out_body
        GROUP BY jo_no, mr_no, matcode
) AS min_out_body ON min_out_body.matcode = mintemp.matcode 
                        AND min_out_body.jo_no = mintemp.jo_no 
                        AND min_out_body.mr_no = mintemp.mr_no
LEFT JOIN (
        SELECT  jo_no, mr_no, matcode, SUM(out_qty) AS  eu_min_out_body_sum
        FROM    eu_min_out_body
        GROUP BY jo_no, mr_no, matcode
) AS eu_min_out_body ON eu_min_out_body.matcode = mintemp.matcode 
                        AND eu_min_out_body.jo_no = mintemp.jo_no 
                        AND eu_min_out_body.mr_no = mintemp.mr_no
WHERE   mintemp.matcode = 'xxx'
        AND (mintemp.min_qty
            - IFNULL(min_out_body.min_out_body_sum,0)
            - IFNULL(eu_min_out_body.eu_min_out_body_sum,0)
        ) > 0

If this query performance slow then check the indexing on your server (EXPLAIN). The important thing is that with SQL server we have to work with small number of huge result sets instead of a lot of small subqueries.

StackExchange Code Review Q#36847, answer score: 4