Skip to content
Advertisement

Select all groups that contain all specified values in a certain column

Say I have the following table:

CaseRef NotificationReason NotificationDate
123 SCHEDULED 2022-01-01
234 SCHEDULED 2022-01-02
312 SCHEDULED 2022-01-01
123 RESCHEDULED 2022-01-02
123 DECIDED 2022-01-03
234 DECIDED 2022-01-02

If I want to return only rows that have a CaseRef that has both a SCHEDULED and a DECIDED value in NotificationReason.

CaseRef NotificationReason NotificationDate
234 SCHEDULED 2022-01-02
234 DECIDED 2022-01-02
123 SCHEDULED 2022-01-01
123 RESCHEDULED 2022-01-02
123 DECIDED 2022-01-03

I wrote the below which works fine and only excludes the one row, but I was wondering if this is the most efficient way of constructing such a query?

Thanks.

Advertisement

Answer

Your query should be reasonably fast for checking two statuses. But in case you want to check many statuses an alternate would be:

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement