For VFP native coders I think this would make it a valid SQL for VFP too :
select a.id,a.docno,a.docdt,;
b.id as childid, b.duedt, b.amount ;
from parent a left join child b on a.id = b.parentid ;
where b.DueDt = ;
(select MAX(DueDt) from child c where c.parentid = b.parentid)
PS: Used DueDt instead of id just thinking they might be entered out of order.
Cetin
>If the backend is Firebird, the following SQL is valid:
>
>select a.id,a.docno,a.docdt,b.id as childid, b.duedt, b.amount from parent a left join child b
>on a.id = b.parentid where b.id = (select MAX(id) from sample_child where parentid = a.id)
>
>If is VFP, I think that this will be available in VFP9 ("Queries of this type not supported" error message)
>
>
>
>>Hi all
>>
>>I have a parent-child like the following. It is a simple representation of the actual.
>>
>>---Parent
>>ID 5
>>Docno 123
>>Docdt 12/12/03
>>
>>---Child 1
>>ID 456
>>ParentID 5
>>DueDt 31/01/04
>>Amount 75,000
>>
>>---Child 2
>>ID 489
>>ParentID 5
>>DueDt 30/04/04
>>Amount 50,000
>>
>>
>>How to write the SQL to return. I don't want the older child records for the parent
>>
>>ID 5
>>Docno 123
>>Docdt 12/12/03
>>ChildID 489
>>DueDt 30/04/04
>>Amount 50,000
>>
>>
>>Please advise.