How do I list all (unique) ID's from the below table that have an F in INSTRUCTION for TYPE PA unless CA also has an F in INSTRUCTION

I tried the following WHERE table.TYPE)="PA") AND (table.INSTRUCTION)="F")
But this also includes records having INSTRUCTION F for TYPE CA (which I don't want in the results)

The desired outcome is as follows:

CodePudding user response：

Start with a query which selects 'PA' type and 'F' instruction rows. Then left join that to a subquery which selects 'CA' type and 'F' instruction rows. And, in the main query, limit the selected rows to those where the subquery id is Null.

SELECT y.ID
FROM
    YourTable AS y
    LEFT JOIN
    (
        SELECT y2.ID
        FROM YourTable AS y2
        WHERE
                y2.TYPE='CA'
            AND y2.INSTRUCTION='F'
    ) AS sub
    ON y.ID = sub.ID
WHERE
        y.TYPE='PA'
    AND y.INSTRUCTION='F'
    AND sub.ID Is Null;