Optimized Query

Question

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 = mintemp.matcode
                              and jo_no = mintemp.jo_no
                              and mr_no = mintemp.mr_no)) > 0

I can get the result, but is there any way to simplify the query and reduce the process time?

This code is not in working order. The query references min.min_no which does not exist. I vote to close... if you edit your question (to reopen it) I suggest that you change your table name 'min' to be something other than a reserved word which is a PITA to use as a table name. Also, please add a description of what you expect this SELECT statement to return. — rolfl
– rolfl, Commented Dec 7, 2013 at 14:16
Here is an SQLFiddle I tried to use, but failed.....: sqlfiddle.com/#!2/8eaee/2 — rolfl
– rolfl, Commented Dec 7, 2013 at 14:20
sorry for the table name, i have a `` between the table name to avoid any confusion. As for the result what i want i have state it clearly in question, i am able to get the result as what i am aiming at, but i would like to have another queries that probably can speed up, simplify the process and query — user33133
– user33133, Commented Dec 7, 2013 at 14:53
You are missing the point... your query does not work... there is no column 'min_no'. CodeReview is for working code. Fix your query, and while you are at it, make it easier to use with a table name other than 'min'. — rolfl
– rolfl, Commented Dec 7, 2013 at 14:55
sorry, i have correct the query and has been tested in sqlfiddle.com/#!2/4c8ee/2 — user33133
– user33133, Commented Dec 7, 2013 at 15:06

Peter Kiss · Accepted Answer · 2013-12-08 06:44:15Z

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.

That's also more or less how I would do it. I'd incorporate @rolfl's suggestion to use HAVING total > 0 to simplify where WHERE clause. — 200_success
– 200_success, Commented Dec 8, 2013 at 7:07
With strict SQL query rules you can not have HAVING without a GROUP BY statement (or it will be executed as WHERE) and i think alias names can not be used in the expression. I'm not sure about these things i'm mostly working with MSSQL. — Peter Kiss
– Peter Kiss, Commented Dec 8, 2013 at 7:57
You're right. MySQL ≥ 5.0.2 supports HAVING total > 0 even though it's not standard SQL. — 200_success
– 200_success, Commented Dec 8, 2013 at 8:05

Malachi · Accepted Answer · 2014-01-28 18:00:11Z

I have had a play with your query. I don't believe there is a way to do the query without the sub-selects in the select fields... but, you can use the having to avoid the duplication of the code (and processing effort) to limit your results to just those with a positive target. Additionally, because there may be values with different jo_no and mr_no combinations, you should also select those values too:

SELECT
mintemp.matcode,
mintemp.jo_no,
mintemp.mr_no,
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'
HAVING total > 0

I have updated the fiddle:

http://sqlfiddle.com/#!2/5c9de/7

Stack Exchange Network

Optimized Query

2 Answers 2

You must log in to answer this question.

Hot Network Questions

Optimized Query

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions