Try this:
SELECT a.*, b.nameinfo...
FROM id a LEFT JOIN names b
ON a.id = b.id
WHERE b.id IS NULL OR b.effective_date IN
(SELECT MAX(f.effective_date) FROM names f WHERE f.id = b.id)
>I tried your solution and got back an error "SQL expression is too complex." I even simplified the query down for testing purposes:
>
>SELECT a.*, b.nameinfo...
>FROM id a LEFT JOIN names b
>ON a.id = b.id
>WHERE (b.id IS NULL OR TTOC(b.effective_date, 1) =
>(SELECT MAX(TTOC(f.effective_date, 1)) FROM names f WHERE f.id = b.id))
>
>I am not sure what is too complex for VFP ;-) This is exactly what I was looking for but I don't know why it doesn't work.
Mark McCasland
Midlothian, TX USA