# 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 = 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. Dec 7, 2013 at 14:16
• Here is an SQLFiddle I tried to use, but failed.....: sqlfiddle.com/#!2/8eaee/2 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 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'. Dec 7, 2013 at 14:55
• sorry, i have correct the query and has been tested in sqlfiddle.com/#!2/4c8ee/2 Dec 7, 2013 at 15:06

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. 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. Dec 8, 2013 at 7:57
• You're right. MySQL ≥ 5.0.2 supports HAVING total > 0 even though it's not standard SQL. Dec 8, 2013 at 8:05

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