thanks for the replies

which is faster, a "subselect" or "exists"?

or does it matter?

i've shy'ed away from sub-selects because of speed issues (fears)?

thanks again
kelly

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> Oops, didn't notice it was a self join.
>
> What I now think you want, is
>
> 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 * from reliefd o where t.equipmentkey=o.equipmentkey and o.datetested<='2005')
> order by t.tagnumber
>
> I guess you don't like subselects (although I don't understand why), using a CTE would get you the same result:
>
> WITH tmp(equipmentkey)
> AS
> (SELECT DISTINCT equipmentkey FROM reliefd
> WHERE datetested <= '2005')
> SELECT t.plantkey, t.mostrecent, t.tagnumber, t.datetested, t.equipmentkey
> FROM reliefd t
> JOIN tmp o ON t.equipmentkey = o.equipmentkey
> WHERE t.mostrecent='T' AND
> t.PlantKey='20030319103909097704'
>
> HTH,
> Set
>