Why is my query inserting the same values when I have added a 'not exists' parameter that-CodePudding

My query should stop inserting values, as the not exists statement is satisfied (I have checked both tables) and matching incidents exist in both tables, any ideas why values are still being returned? Here is the code:

INSERT INTO
    odwh_system.ead_incident_credit_control_s
(
    incident
)   
SELECT DISTINCT
    tp.incident
FROM 
    odwh_data.ead_incident_status_audit_s ei
    INNER JOIN odwh_data.ead_incident_s tp ON ei.incident=tp.incident
WHERE   
    ei.status = 6 
OR
    ei.status = 7 

AND NOT EXISTS
        (
        SELECT
            true
        FROM
            odwh_system.ead_incident_credit_control_s ead
        WHERE
            ead.incident = tp.incident
        )
AND EXISTS
        (
        SELECT
            true
        FROM
            odwh_work.ead_incident_tp_s tp
        WHERE
            tp.incident = ei.incident
        );

CodePudding user response：

dont reuse table aliases
use sane aliases
avoid AND/OR conflicts; prefer IN()

INSERT INTO odwh_system.ead_incident_credit_control_s (incident)
SELECT -- DISTINCT
    tp.incident
FROM odwh_data.ead_incident_s dtp
WHERE NOT EXISTS (
        SELECT *
        FROM odwh_system.ead_incident_credit_control_s sic
        WHERE sic.incident = dtp.incident
        )
AND EXISTS (
        SELECT *
        FROM odwh_work.ead_incident_tp_s wtp
        JOIN odwh_data.ead_incident_status_audit_s dis ON wtp.incident = dis.incident AND dis.status IN (6 ,7)
        WHERE wtp.incident = dtp.incident
        );