Why is my SQL ALL operator returning no results?
I am using TSQL and want to select data from a table of Persons based on their StatusID field, and if all the person’s Discharge Dates are prior to today.
A person can have multiple discharge dates. I am trying to use a subquery with ALL to check if all their discharge dates are prior to today but it isn’t working. What am I doing wrong? Or is there a better way than SQL ALL?
Here is code sample I tried:
select distinct per.PersonNo
from PersonInfo per, Cases cas
left join Cases cas on per.PersonNo = cas.PersonNo
where (per.StatusID = 3012
and per.PersonNo = ALL (SELECT cas.PersonNo FROM Cases cas WHERE cas.DischargeDate < getdate() ))
I know some of my test data has the right StatusID and all their discharge dates are prior to today, and they are not getting selected but they should be. My query returns no results.