Kelly,

> it gave me something to google using
>
> i settled on:
>
> select t.plantkey, t.mostrecent, t.tagnumber, t.datetested, t.equipmentkey
> from reliefd t where t.mostrecent='T' and
> t.PlantKey='20030319103909097704' and exists (select 1 from reliefd p
> where p.datetested<='2005' and t.equipmentkey = p.equipmentkey) order
> by t.tagnumber
>
> that returns the result i need and i think the
>
> exists ( select 1....) is the most efficient
>
> thoughts?

EXISTS will ** be faster** that IN (SELECT ... FROM ...) in 98% of cases -- it depends on the size of the target table.

But if the IN clause has defined list of values, up 1500 in the case of Windows builds, [eg. IN (1,2,3,4, ...)] the answer is not as "black and white" but will likely favor the defined list IN syntax.


Sean