Yes the BackEnd is FireBird. I was first thinking in terms of implementing this as a VFPView now that this query is particularly FB then maybe I will implement in Report Manager directly. Thanks nevertheless for the idea, I will also try to make similar in VFP for view.
>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.