Mysql – Converting SELECT subquery to LEFT JOIN doesnt return same value

MySQL

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

Also i need to see only result where gross_end_out > 0

EDIT: As requested i add table structure:

cargo_header_receive:

CREATE TABLE `cargo_header_receive` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `docNr` VARCHAR(50) NULL DEFAULT '0',

    PRIMARY KEY (`id`),
    INDEX `docNr` (`docNr`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

cargo_line_receive:

CREATE TABLE `cargo_line_receive` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `docNr` VARCHAR(250) NOT NULL DEFAULT '',
    `batchNo` VARCHAR(250) NULL DEFAULT '',
    `serialNo` VARCHAR(250) NULL DEFAULT '',
    `productNr` VARCHAR(250) NOT NULL DEFAULT '',   
    `activityDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    `status` INT(11) NOT NULL DEFAULT '0',
    `orgLine` INT(11) NOT NULL DEFAULT '0',

    PRIMARY KEY (`id`),
    INDEX `docNr` (`docNr`),
    INDEX `serialNo` (`serialNo`),
    INDEX `productNr` (`productNr`),
    INDEX `status` (`status`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

item_ledger_entry:

CREATE TABLE `item_ledger_entry` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `docNr` VARCHAR(250) NOT NULL DEFAULT '0',
    `cargoLine` INT(11) NULL DEFAULT NULL,
    `activityDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    `productNr` VARCHAR(250) NOT NULL,
    `orgLine` INT(11) NULL DEFAULT '0',
    `status` INT(11) NOT NULL DEFAULT '0',  
    `serialNo` VARCHAR(250) NULL DEFAULT '0',   
    `gross` DECIMAL(20,10) NOT NULL DEFAULT '0.0000000000',

    PRIMARY KEY (`id`),
    INDEX `cargoLine` (`cargoLine`),
    INDEX `orgLine` (`orgLine`),
    INDEX `docNr` (`docNr`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

n_items:

CREATE TABLE `n_items` (
    `code` VARCHAR(250) NOT NULL,

    PRIMARY KEY (`code`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

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

Thanks in advance!

EDIT FOR Nomis:

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,

    ( 
        SELECT SUM(ile.gross)
        FROM cargo_line_receive AS ile 
        WHERE clr.docNr=ile.docNr 
        AND clr.serialNo=ile.serialNo 
        AND clr.productNr=ile.productNr 
        AND (ile.id=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-01' 
    ) AS gross_start_in,    

    ( 
        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-01' 
        AND ile.status='40' 
    ) AS gross_start_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

Best Answer

1/What are you doing wrong : This is the easy part, you moved problematic subqueries to a left join still doing the subqueries. Then you removed the function grouping the results as Akina pointed in the comments, resulting in multiple lines joining with multiple lines

2/What to do to solve the immediate issue at hand (bad idea) : just put back the sum function SELECT SUM(ile.gross). With a little luck it should gave you back the 6000. But it should perform worst than the first query since it basically does the same with more join.

3/The real solution : start back from the initial query and make it lighter.

I think should look like something like that :

SELECT SUM(ile.gross) AS gross_end_out
FROM item_ledger_entry AS ile
JOIN cargo_line_receive AS clr 
ON 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 ((clr.status='20' OR clr.status='30') && clr.status!='40')
AND Date_format(clr.activityDate, '%Y-%m-%d') <= '2019-08-05'
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'
GROUP BY clr.id, clr.docNr, clr.batchNo, clr.serialNo, clr.productNr -- not sure how this part play out in the initial query, may need to be removed
HAVING SUM(ile.gross) > 0

Basically, all others tables than item_ledger_entry and cargo_line_receiver serve no purpose : no conditions, no data from them. So remove them and directly join cargo_line_receiver to the actually useful part of the query.

I didn't test my example so it may need a little tinkering to make it work as you want, but it should be pretty close. And if you still have performance issue by then, your query will be a LOT easier for people to help you with ^^

Edit for the edit :

SELECT
SUM(IF( ile.status='40' ,ile.gross,0)) AS gross_end_out,
SUM(IF( (Date_format(ile.activityDate, '%Y-%m-%d') < '2019-08-01') ,ile.gross,0)) AS gross_start_in,
SUM(IF( (Date_format(ile.activityDate, '%Y-%m-%d') <  '2019-08-01'  AND ile.status='40') ,ile.gross,0)) AS gross_start_out
FROM item_ledger_entry AS ile
JOIN cargo_line_receive AS clr 
ON 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 ((clr.status='20' OR clr.status='30') && clr.status!='40')
AND Date_format(clr.activityDate, '%Y-%m-%d') <= '2019-08-05'
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' --don't forget to remove the status condition
GROUP BY clr.id, clr.docNr, clr.batchNo, clr.serialNo, clr.productNr
HAVING SUM(ile.gross) > 0

Related Solutions

Mysql – get column from too many tables in thesql

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Mysql – Are two indexes needed

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile, but most likely a full-length index will be just fine.

Indexes are typically B-trees, and a seek will jump to the right location about equally quickly whether you have 10 or 100 entries per page. Scans are another matter, but I'd start with the simplest solution and add an extra index only if performance proves inadequate in practice.

Best Answer

Related Solutions

Mysql – get column from too many tables in thesql

Mysql – Are two indexes needed

Related Question