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 [email protected], "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
>
