3
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – rolfl Dec 7 '13 at 14:16
  • 1
    \$\begingroup\$ Here is an SQLFiddle I tried to use, but failed.....: sqlfiddle.com/#!2/8eaee/2 \$\endgroup\$ – rolfl Dec 7 '13 at 14:20
  • \$\begingroup\$ 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 \$\endgroup\$ – user33133 Dec 7 '13 at 14:53
  • 1
    \$\begingroup\$ 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'. \$\endgroup\$ – rolfl Dec 7 '13 at 14:55
  • \$\begingroup\$ sorry, i have correct the query and has been tested in sqlfiddle.com/#!2/4c8ee/2 \$\endgroup\$ – user33133 Dec 7 '13 at 15:06
4
\$\begingroup\$
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.

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – 200_success Dec 8 '13 at 7:07
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – Peter Kiss Dec 8 '13 at 7:57
  • \$\begingroup\$ You're right. MySQL ≥ 5.0.2 supports HAVING total > 0 even though it's not standard SQL. \$\endgroup\$ – 200_success Dec 8 '13 at 8:05
3
\$\begingroup\$

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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.