>>>I have two tables:
>>>
>>>patients: with single record per patient and
>>>referrals: with any number of referral records per patient. Each referall record has the patient id field (MNO - relating to MNO field in patients table) as well as a referral date (refdate) field
>>>
>>>I want an Select SQL statement which will list all patients to the left of the join and, on the right of the join, ONLY the latest referral record relating to each the patient - i.e. if the patient has 10 referral records, only the most recent to be included.
>>>
>>>Is this possible in SQL? Any ideas Gratefully Received!
>>

select * from main left join child ;
>> on main.id = child.id ;
>> group by main.id ;
>> into cursor myCursor

Cetin
>
>Will this ensure that the referral record is the most recent?
>
>I was going to suggest something like:
>
>SELECT Patient.*, Referral.refdate;
> FROM Patient,referral ;
> WHERE Referral.Patientid = Patient.MNO;
> AND Referral.RefDate IN
> (SELECT TOP 1 RefDate ;
> FROM Referral;
> WHERE Referral.Patientid = Patient.MNO;
> ORDER BY refdate DESC);
> INTO CURSOR myCursor
I got "most recent" as most recent entered not most recent by date. Logically it's true for a patient recording system. Otherwise w/o a subquery :

select max(child.visit_date) as lastvisit,* ;
    from main left join child ;
	on main.id = child.id ;
	group by main.id ;
	into cursor mycursor

Cetin