>hi all--

Hi Kelly!

>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

>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

>but i don't really want to use the nested selects
>
>is there something else i can do with the join?

My guess is that you want something like:

select t.plantkey, t.mostrecent, t.tagnumber, t.datetested, t.equipmentkey, max(o.datetested) as LastTested
from reliefd t
join reliefd o on t.equipmentkey=o.equipmentkey
where t.mostrecent='T' and
t.PlantKey='20030319103909097704' and
o.datetested<='2005'
group by 1,2,3,4,5
order by t.tagnumber

Alternatively, you might want list(o.datetested) rather than max(o.datetested).

HTH,
Set