Another rewrite subquery problem

[Piet]

Hi,

Rewriting a report that displays the details of an address and that of the last visits (if any) on an address I need help to rewrite the query.

select * from address a
join visits b on a.addressnr = b.addressnr
where b.visitdate = (select max(v. visitdate) from visits v where v.addressnr = a.addressnr group by v.address) or b.visitdate is null
order by a.addressnr

Table address holds all address properties and table visits all properties of the visit. All attrib-utes of the last visit need to be reported.

I’m migrating to MySQL 4.0.22 and it no option to move forward to a 4.1 environment.

Many thanks

Piet.