Hi Albert,

Yours and my queries are the same but my is a bit more flexible as it allows to do checks on multiple columns and also it usually performs better. For a single column yours and mine should be the same and you can compare the speed in both.

Thanks.

>Hi,
>
>Thanks. The table does not use nulls for date closed.
>
>I just looked up "IN" vs. "EXISTS" - I have only used IN before - would my original proposed query then actually not have been a correlated subquery but instead was it technically just a subquery? I am thinking it would have been and perhaps the subquery would have created a large result set for each outer query row (the inner query returns about 4000 rows). Whereas your query would have only returned 1 row for each outer row query. Just asking so I can understand it better.
>
>Also, it looks like you have to use table aliases in a correlated subquery.
>
>Here was my original proposed query:
>
>

>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
>

>
>
>>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