MK01111000
MK01111000

Reputation: 822

How do I query based on values in multiple columns

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

ID TYPE INSTRUCTION
12 CA 203
12 LT F
12 PA 408
15 CA F
15 LT F
15 PA F
24 CA 608
24 LT 309
24 PA 75
133 CA 468
133 LT F
133 PA F

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)

ID
15
133

The desired outcome is as follows:

ID
133

Upvotes: 1

Views: 37

Answers (1)

HansUp
HansUp

Reputation: 97101

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;

Upvotes: 1

Related Questions