hi all--- thanks for the replies... after reading them and doing some google'ing now that i had an idea, i think i'll settle 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 expect and, from what i understand) the exists (select 1.....) is 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 >
