hi--

thanks for the replies.

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?

thanks
kelly

--- In firebird-support@yahoogroups.com, "bwc3068" <avert@...> wrote:
>
> hi all--
>
> (always a great place to get answers!!)
>
> here is the sql I'd prefer to use (a join)
>
> select distinct t.plantkey, t.mostrecent, t.tagnumber, t.datetested, t.equipmentkey, o.equipmentkey, o.datetested from reliefd t
> join reliefd o
> on t.equipmentkey=o.equipmentkey
> where
> t.mostrecent='T' and
> t.PlantKey='20030319103909097704' and
> o.datetested<='2005'
> order by
> t.tagnumber
>
> the problem is, the distinct is for the entire returned records (which are all unique). it returns 224 records
>
> PLANTKEY MOSTRECENT TAGNUMBER DATETESTED EQUIPMENTKEY EQUIPMENTKEY DATETESTED
>
> 20030319103909097704 T PSV-100C 2010/02/21 20031219131654053330 20031219131654053330 2003/12/04
> 20030319103909097704 T PSV-100C 2010/02/21 20031219131654053330 20031219131654053330 2004/12/01
>
>
> what i really want is
>
> distinct on the field t.equipmentkey
>
> i can get the proper result (124 records) with the nested select shown below
>
> select t.plantkey, t.mostrecent, t.tagnumber, t.datetested, t.equipmentkey from reliefd t
> where
> t.mostrecent='T' and
> t.PlantKey='20030319103909097704' and
> t.equipmentkey in ( select equipmentkey from reliefd where datetested<='2005')
> order by
> t.tagnumber
>
>
> PLANTKEY MOSTRECENT TAGNUMBER DATETESTED EQUIPMENTKEY
>
> 20030319103909097704 T PSV-100C 2010/02/21 20031219131654053330
>
> but i don't really want to use the nested selects
>
> is there something else i can do with the join?
>
> thanks
> kelly
>