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
