I am trying to SQL some of my files for a report. In fox code I have the following situation (this is what I am trying to accomplish whth the SQL statment).

SELECT MoveHst
SET RELATION TO ID INTO Child
SET RELTION TO Home INTO Home ADDITIVE
SELECT Home
SET RELATION TO SW_Code INTO Employee

All the above relations are based on integer IDs.

I setup the following SQL statement:

SELECT Movehst.ID AS cId, Movehst.In_Date AS InDate, ;
Movehst.Out_Date AS OutDate, Movehst.Home AS hId, ;
Child.Full_Name AS cFull_Name, Child.Last_First AS cLastFirst, ;
Child.Birthday AS cBirthDay, ;
Child.Intake AS cIntake, Child.Terminate AS cTerminate, ;
ALLTRIM(Child.Infnt1_F) + " " + ALLTRIM(Child.Infnt1_L) AS Infant1, ;
Child.Infnt1_Dob AS Infbday1, ;
ALLTRIM(Child.Infnt2_F) + " " + ALLTRIM(Child.Infnt2_L) AS Infant2, ;
Child.Infnt2_Dob AS Infbday2, ;
ALLTRIM(Child.Infnt3_F) + " " + ALLTRIM(Child.Infnt3_L) AS Infant3, ;
Child.Infnt3_Dob AS Infbday3, ;
Home.Last_First AS hLastFirst, ;
Home.H_Full_Nm AS hFull_nm, Home.FFA_Cert AS hCertDate, ;
Home.FFA_Decert AS hDecert, Home.Beds AS hBeds, 100 AS Bed_No, ;
Home.SW_Code AS hSW_Code, ;
Employee.Full_Name AS eFull_Name, Employee.Last_First AS eLastFirst, ;
Employee.Office AS eOffice ;
FROM main!movehst LEFT OUTER JOIN main!child;
LEFT OUTER JOIN main!home ;
LEFT OUTER JOIN main!Employee ;
ON Home.SW_Code = Employee.ID ;
ON Movehst.home = Home.id ;
ON Movehst.id = Child.id


The results I got did connect to the Child but the home was the same for all records. The employee was the correct one for the one home that was reported.

I am stumped. I am sure it is something in the from clause syntax but I can find no examples in the documentation to guide me in the correct direction. I tryed the view builder and it produced the same code I did.

Thanks in advanced
Terry