Say I have the following table:

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

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