DL - Query to check for missing gas cut stamping details

A reminder of why I used a combination of JOIN and INNER JOIN

This serves as a decent example of this.

# ANY test_number WITH ITS ASSOCIATED mes_plateline NUMBER THAT HAS MOC_Stamping_Reqd IN mes_manufacturing_order
# SHOULD APPEAR IN mes_gas_cut_stamping.
SET @test_date :="2020-09-01";

SELECT mes_plateline.doe, mes_plateline.plateline, mes_plateline.test_number,
mes_gas_cut_stamping.test_number AS in_gascut_stamping, # CAN BE REMOVED LATER 

IF (mes_manufacturing_order.MOC_Stamping_Reqd = 'Y','YES - THIS IS A BAD SIGN','NO - OK') AS Stamping_Required # CAN BE REMOVED LATER

# WE USE A LEFT JOIN 1ST BECAUSE THE PLATELINE FROM MES_PLATELINE MIGHT NOT BE IN MES_GAS_CUT_STAMPING
# WE CAN THEN USE A JOIN BECAUSE THE SALESORDER FROM MES_PLATELINE WILL ALWAYS BE IN MES_MANUFACTURING_ORDER

FROM mes_plateline LEFT JOIN mes_gas_cut_stamping ON (mes_plateline.plateline = mes_gas_cut_stamping.plateline)
JOIN mes_manufacturing_order ON (mes_plateline.salesorder = mes_manufacturing_order.MO_salesOrderNo)

WHERE mes_plateline.doe >= @test_date AND LENGTH(mes_plateline.test_number)>6
AND mes_gas_cut_stamping.test_number IS NULL #AND mes_manufacturing_order.MOC_Stamping_Reqd = "Y" # CHANGE TO N TO VERIFY

ORDER BY doe DESC
LIMIT 50;

# Y WILL SHOW ONLY PLATES THAT SHOULD HAVE DETAILS BUT DO NOT.
# N WILL SHOW PLATES THAT SHOULD NOT HAVE STAMPING DETAILS AND DO NOT.
# COMMENT OUT LAST 'AND' TO SHOW BOTH

---

The following query will return only the offending test numbers;

SET @temp_date :="2020-09-01";

SELECT mes_plateline.test_number

FROM mes_plateline LEFT JOIN mes_gas_cut_stamping ON (mes_plateline.plateline = mes_gas_cut_stamping.plateline)
JOIN mes_manufacturing_order ON (mes_plateline.salesorder = mes_manufacturing_order.MO_salesOrderNo)

WHERE mes_plateline.doe >= @temp_date AND LENGTH(mes_plateline.test_number)>6
AND mes_gas_cut_stamping.test_number IS NULL AND mes_manufacturing_order.MOC_Stamping_Reqd = "Y"

ORDER BY mes_plateline.test_number ASC LIMIT 50;

---

The following will set the processed flag to 0 in mes.gas_cut_process for all test_numbers returned from the above query; (TO BE UPDATED)

UPDATE mes_gas_cut_process

SET mes_gas_cut_process.processed = 0

WHERE mes_gas_cut_process.pieceno IN

        (
        SELECT mes_plateline.test_number

        FROM mes_plateline LEFT JOIN mes_gas_cut_stamping ON 

        (mes_plateline.plateline = mes_gas_cut_stamping.plateline)
        JOIN mes_manufacturing_order

        ON (mes_plateline.salesorder = mes_manufacturing_order.MO_salesOrderNo)

        WHERE mes_plateline.doe >= @temp_date AND LENGTH(mes_plateline.test_number)>6
AND mes_gas_cut_stamping.test_number IS NULL AND mes_manufacturing_order.MOC_Stamping_Reqd = "Y" LIMIT 50
        );