>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?
Agree that EXISTS is better here because it makes really clear what you're looking for. I suspect it'll get optimized anyway into one query that collects the whole set and then is filtered for each record. So I'd be surprised if there's a significant speed difference.
Tamar