#StackBounty: #mysql Converting SELECT subquery to LEFT JOIN doesnt return same value

Bounty: 50

as title says i have an issue converting SELECT subquery to LEFT JOIN doesnt return same value šŸ™

I need to change this as subquery really slows down query.

Here are my original query (gross_end_out returns 6000):

SELECT  

    ( 
        SELECT SUM(ile.gross)                   
        FROM item_ledger_entry AS ile 
        WHERE clr.docNr=ile.docNr 
        AND clr.serialNo=ile.serialNo 
        AND clr.productNr=ile.productNr 
        AND (ile.cargoLine=clr.id OR ile.orgLine=clr.id) 
        AND ile.id NOT IN (
                            SELECT MIN(zz.id) 
                            FROM item_ledger_entry AS zz 
                            WHERE zz.docNr=ile.docNr 
                            AND zz.serialNo=ile.serialNo 
                            AND zz.productNr=ile.productNr
                          ) 
        AND Date_format(ile.activityDate, '%Y-%m-%d') <= '2019-08-05' 
        AND ile.status='40' 
    ) AS gross_end_out

FROM cargo_line_receive AS clr 

LEFT JOIN cargo_header_receive AS chr 
ON clr.docNr=chr.docNr  

LEFT JOIN n_items AS ni 
ON clr.productNr=ni.code 

LEFT JOIN item_ledger_entry AS i 
ON clr.docNr=i.docNr AND i.status=40 
AND (i.id=clr.id OR i.orgLine=clr.id)   


WHERE ((clr.status='20' OR clr.status='30') && clr.status!='40')  

AND Date_format(clr.activityDate, '%Y-%m-%d') <= '2019-08-05' 

GROUP BY clr.id, clr.docNr, clr.batchNo, clr.serialNo, clr.productNr
ORDER BY clr.activityDate DESC, clr.id DESC

Here are new query (gross_end_out returns 18000):

SELECT  

    SUM(a.gross) AS gross_end_out

FROM cargo_line_receive AS clr 

LEFT JOIN cargo_header_receive AS chr 
ON clr.docNr=chr.docNr  

LEFT JOIN n_items AS ni 
ON clr.productNr=ni.code 

LEFT JOIN item_ledger_entry AS i 
ON clr.docNr=i.docNr AND i.status=40 
AND (i.id=clr.id OR i.orgLine=clr.id)   



LEFT JOIN (

    SELECT ile.gross, 
    ile.docNr, ile.serialNo, ile.productNr, ile.cargoLine, ile.orgLine
    FROM item_ledger_entry AS ile 
    WHERE ile.id NOT IN (
                            SELECT MIN(zz.id) 
                            FROM item_ledger_entry AS zz 
                            WHERE zz.docNr=ile.docNr 
                            AND zz.serialNo=ile.serialNo 
                            AND zz.productNr=ile.productNr
                        ) 
    AND Date_format(ile.activityDate, '%Y-%m-%d') <= '2019-08-05' 
    AND ile.status='40'

) AS a 
ON clr.docNr=a.docNr 
AND clr.serialNo=a.serialNo 
AND clr.productNr=a.productNr 
AND (a.cargoLine=clr.id OR a.orgLine=clr.id)

WHERE ((clr.status='20' OR clr.status='30') AND clr.status!='40')  

AND Date_format(clr.activityDate, '%Y-%m-%d') <= '2019-08-05' 

GROUP BY clr.id, clr.docNr, clr.batchNo, clr.serialNo, clr.productNr
ORDER BY clr.activityDate DESC, clr.id DESC

What em i doing wrong?
Also i cant use gross_end_out in WHERE

Thanks in advance!


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.