SQL Returning all rows for certain column that different values in certain field grouped by another-CodePudding

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?

SELECT * 
FROM @Notifications
WHERE CaseRef IN (SELECT CaseRef FROM @Notifications
                  WHERE NotificationReason = 'SCHEDULED')
  AND CaseRef IN (SELECT CaseRef FROM @Notifications
                  WHERE NotificationReason = 'DECIDED')
ORDER BY CaseRef DESC

Thanks.

CodePudding user response：

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

select *
from t
where caseref in (
    select caseref
    from t
    where notificationreason in ('SCHEDULED', 'DECIDED')
    group by caseref
    having count(distinct notificationreason) = 2
)

CodePudding user response：

Your query is OK but returns and additional row with NotificationReason RESCHEDULED so it should be:

select * FROM Notifications
where CaseRef in (select CaseRef from Notifications
where NotificationReason = 'SCHEDULED')
AND CaseRef in (select CaseRef from Notifications
where NotificationReason = 'DECIDED')
and NotificationReason<>'RESCHEDULED'
order by CaseRef desc

fiddler http://sqlfiddle.com/#!18/2bf412/17 And if you want to know the efficiency of a query check the execution plan