MySQL statement not including Null values

Question

SELECT DISTINCT
    CAST(`t1`.`ImportedOn` AS DATE) AS `DispatchDate`,
    `t1`.`Order` AS `order`,
    `t1`.`Operation` AS `QbOP`,
    `t1`.`UserOpStatus` AS `OpsAway`,
    `t1`.`UnloadingPoint` AS `Location`,
    `t1`.`WorkCenter` AS `QbWC`,
    `t1`.`LatestStartDate` AS `QbStartDay`,
    `t1`.`LatestStartTime` AS `QbStartTime`,
    `t2`.`Operation` AS `RcOP`,
    `t2`.`WorkCenter` AS `RcWC`,
    `t2`.`LatestStartDate` AS `lateststartdate`,
    `t2`.`LatestStartTime` AS `lateststarttime`,
    `wc`.`CellName` AS `CellName`,
    IF((`wooc`.`ConfirmationDate` IS NULL),
        'Open',
        IF((`t1`.`UserOpStatus` = 'run'),
            'Running',
            'Done')) AS `Status`
FROM
    (((`workorderoperationschedule` `t1`
    LEFT JOIN `workorderoperationschedule` `t2` ON (((`t1`.`Order` = `t2`.`Order`)
        AND (`t2`.`Operation` > `t1`.`Operation`)
        AND (`t2`.`Operation` < (`t1`.`Operation` + 11)))))
    LEFT JOIN `workcenters` `wc` ON ((`wc`.`WorkCenter` = `t1`.`WorkCenter`)))
    LEFT JOIN `workorderoperationconfirmations` `wooc` ON (((`wooc`.`Order` = `t1`.`Order`)
        AND (`wooc`.`Operation` = `t1`.`Operation`))))
WHERE
    ((`t1`.`LatestStartDate` <= CAST(`t1`.`ImportedOn` AS DATE))
        AND (`t1`.`ImportedOn` = (SELECT 
            MAX(`t1`.`ImportedOn`)
        FROM
            `workorderoperationschedule` `t1`))
        AND (`t2`.`ImportedOn` = (SELECT 
            MAX(`t1`.`ImportedOn`)
        FROM
            `workorderoperationschedule` `t1`)))
ORDER BY `Status` DESC , `t1`.`WorkCenter` , `t2`.`LatestStartDate` , `t2`.`LatestStartTime`

My challenge is that sometimes t2 will not have a value, which is expected, however, i think MySQL is not returning those rows when t2 has not match. I thought a left join would return all values from t1 and only the values from t2 that match but that is not happening

score 1 · Answer 1 · answered Jul 22 '20 at 15:00

If you need a left join then you can't use a colunm of a left joined table in a where condition so try move the t2 condition for max value in the related ON clause

 SELECT DISTINCT
    CAST(`t1`.`ImportedOn` AS DATE) AS `DispatchDate`,
    `t1`.`Order` AS `order`,
    `t1`.`Operation` AS `QbOP`,
    `t1`.`UserOpStatus` AS `OpsAway`,
    `t1`.`UnloadingPoint` AS `Location`,
    `t1`.`WorkCenter` AS `QbWC`,
    `t1`.`LatestStartDate` AS `QbStartDay`,
    `t1`.`LatestStartTime` AS `QbStartTime`,
    `t2`.`Operation` AS `RcOP`,
    `t2`.`WorkCenter` AS `RcWC`,
    `t2`.`LatestStartDate` AS `lateststartdate`,
    `t2`.`LatestStartTime` AS `lateststarttime`,
    `wc`.`CellName` AS `CellName`,
    IF((`wooc`.`ConfirmationDate` IS NULL),
        'Open',
        IF((`t1`.`UserOpStatus` = 'run'),
            'Running',
            'Done')) AS `Status`
FROM
    (((`workorderoperationschedule` `t1`
    LEFT JOIN `workorderoperationschedule` `t2` ON (((`t1`.`Order` = `t2`.`Order`)
        AND (`t2`.`Operation` > `t1`.`Operation`)
        AND (`t2`.`Operation` < (`t1`.`Operation` + 11)))))
        AND (`t2`.`ImportedOn` = (SELECT 
            MAX(`t1`.`ImportedOn`)
        FROM
            `workorderoperationschedule` `t1`))
    LEFT JOIN `workcenters` `wc` ON ((`wc`.`WorkCenter` = `t1`.`WorkCenter`)))
    LEFT JOIN `workorderoperationconfirmations` `wooc` ON (((`wooc`.`Order` = `t1`.`Order`)
        AND (`wooc`.`Operation` = `t1`.`Operation`))))
WHERE
    ((`t1`.`LatestStartDate` <= CAST(`t1`.`ImportedOn` AS DATE))
        AND (`t1`.`ImportedOn` = (SELECT 
            MAX(`t1`.`ImportedOn`)
        FROM
            `workorderoperationschedule` `t1`))
        AND (`t2`.`ImportedOn` = (SELECT 
            MAX(`t1`.`ImportedOn`)
        FROM
            `workorderoperationschedule` `t1`)))
ORDER BY `Status` DESC , `t1`.`WorkCenter` , `t2`.`LatestStartDate` , `t2`.`LatestStartTime`


WHERE
    ((`t1`.`LatestStartDate` <= CAST(`t1`.`ImportedOn` AS DATE))
        AND (`t1`.`ImportedOn` = (SELECT 
            MAX(`t1`.`ImportedOn`)
        FROM
            `workorderoperationschedule` `t1`))
        )

Yep, that'll do it. I was too lazy to copy the whole thing myself. — The Impaler, Jul 22 '20 at 15:07

score 0 · Accepted Answer · answered Jul 22 '20 at 15:05

0

The predicate:

`t2`.`ImportedOn` = (SELECT MAX(`t1`.`ImportedOn`) 
                     FROM `workorderoperationschedule` `t1`)

in the WHERE clause is silently converting your LEFT JOIN into an INNER JOIN.

To make the left join work you need to move this predicate to the ON clause when joinining t2.

answered Jul 22 '20 at 15:05

The Impaler

45,731
9
39
76

Awesome! Thank you so much! – Truth is a person Jul 22 '20 at 15:18

MySQL statement not including Null values

2 Answers2