Multi-Table Join help

[JeffP]

I want only one row for each contact that contains the most recent calendar as enddate and most recent history as ondate. I'm getting multiple rows for each matching contact.

--------------------------------------
select c1.contact ,ca.enddate ,ch.ondate

from ca

join c1 on ca.accountno = c1.accountno

join ch on ca.accountno = ch.accountno

where ca.ondate in (select max(ondate) from ca group by accountno)
------------------------------------------

[zak]

Select col1, Max(CreateDate)
From table1
Where col1 = 'something'
Group By col1
Union
Select col2, Min(CreateDate)
From table1
Where col1 = 'something'
Group By col1


------------
JeffP at 2/7/2002 2:19:29 PM

I want only one row for each contact that contains the most recent calendar as enddate and most recent history as ondate. I'm getting multiple rows for each matching contact.

--------------------------------------
select c1.contact ,ca.enddate ,ch.ondate

from ca

join c1 on ca.accountno = c1.accountno

join ch on ca.accountno = ch.accountno

where ca.ondate in (select max(ondate) from ca group by accountno)
------------------------------------------