Your code seems OK, but I prefer to use NOT EXISTS subquery instead for such problems (especially in SQL Server).

Also, are you using NULL for not closed dates? E.g. I would write: [If you want your code to be eventually portable to SQL Server I suggest to use NULL for the closed date which means file is still opened]

>SELECT ;
>   p.Plaintiff_ID, pf.File_ID ;
>   FROM Plaintiffs  p JOIN Plaintiff_Files pf ;
>      ON  p.Plaintiff_ID == pf.Plaintiff_ID ;
>   WHERE   pf.Date_Closed < ldCutOffDate AND ;
>        NOT EXISTS ( SELECT 1 FROM Plaintiff_Files f WHERE Date_Closed IS NULL and f.Plaintiff_ID == pf.Plaintiff_ID  ) ;
>   INTO CURSOR Results

>Hi,
>
>Here is my situation and want to confirm this is the best way to achieve result set:
>
>- plaintiffs table of plaintiffs
>- each plaintiff can have multiple files open for an accident that (some law firms call these "matters") that are opened and closed at different times
>
>- law firm would like to purge files that have been closed more than 7 years (statute of limitations) but only if the plaintiff does not have any open files still
>
>- here was my query *before* they threw in the need to check for other open files
>
>

>* set a cutoff date in the past
>ldCutOffDate = GOMONTH(DATE(),-84)
>
>SELECT ;
>   Plaintiff_ID, File_ID ;
>   FROM Plaintiffs JOIN Plaintiff_Files ;
>      ON  Plaintiffs.Plaintiff_ID == Plaintiff_Files.Plaintiff_ID ;
>   WHERE NOT EMPTY(Plaintiff_Files.Date_Closed) AND ;
>      Plaintiff_Files.Date_Closed < ldCutOffDate ;
>   INTO CURSOR Results
>

>
>- they then threw in the critieria about the plaintiff not having any open files still so I modified as such
>
>

>SELECT ;
>   Plaintiff_ID, File_ID ;
>   FROM Plaintiffs JOIN Plaintiff_Files ;
>      ON  Plaintiffs.Plaintiff_ID == Plaintiff_Files.Plaintiff_ID ;
>   WHERE NOT EMPTY(Plaintiff_Files.Date_Closed) AND ;
>      Plaintiff_Files.Date_Closed < ldCutOffDate AND ;
>        Plaintiff_ID NOT IN ( SELECT Plaintiff_ID FROM Plaintiff_Files WHERE EMPTY(Date_Closed) ) ;
>   INTO CURSOR Results
>

>
>In the past I would have probably done this in two queries: first figure out the plaintiffs with open files (empty field Date_Closed) and then used a subquery e.g.
>
>

> SELECT Plaintiff_ID ;
>   FROM Plaintiff_Files ;
>   WHERE EMPTY(Date_Closed)  ;
>   INTO CURSOR OpenFiles
>
>SELECT ;
>   Plaintiff_ID, File_ID ;
>   FROM Plaintiffs JOIN Plaintiff_Files ;
>      ON  Plaintiffs.Plaintiff_ID == Plaintiff_Files.Plaintiff_ID ;
>   WHERE NOT EMPTY(Plaintiff_Files.Date_Closed) AND ;
>      Plaintiff_Files.Date_Closed < ldCutOffDate AND ;
>        Plaintiff_ID NOT IN ( SELECT Plaintiff_ID FROM OpenFiles ) ;
>   INTO CURSOR Results
>

>
>I have not tried speed tests - I read somewhere that correlated subqueries are generally slower but I guess it all depends on the size of the data sets.
>
>Any comments?
>
>Albert