You probably meant to say :

select childtable.* from childTable, CrossRefTable, MasterTable where childTable.ChildTable_id = CrossRefTable.ChildTable_id;
 and CrossRefTable.MasterTable_id = MasterTable.MasterTable_id into cursor curSelected

childTable.ChildTable_id would never equal CrossRefTable.MasterTable_id

Every time I run it I get all the Child records matching the crosstable, disregarding the MasterTable value.

What does the trick without using the sub-select is :

select childtable.* ;
  from childTable ;
  inner join CrossRefTable ;
    on childTable.ChildTable_id = CrossRefTable.ChildTable_id
  where CrossRefTable.MasterTable_id = MasterTable.MasterTable_id ;
   into cursor curSelected

In all cases I prefer to specify joins rather than leave it to FoxProw (using 'from childTable, CrossRefTable, MasterTable' construct)

I will try this to see if the sub select is what was causing my problem. It will take a while. I will be back to you. Thanks

>Hi Don,
>
>I may be misreading your select statement, but as far as I can see you can simplify it a lot:
>

>select childtable.* from childTable, CrossRefTable, MasterTable where childTable.ChildTable_id = CrossRefTable.MasterTable_id;
> and CrossRefTable.MasterTable_id = MasterTable.MasterTable_id into cursor curSelected
>

>
>
>>I have a multiple to multiple relationship which means that I have a third cross reference table, made up of the keys (id's) of both tables involved in the relationship.
>>
>>All three tables are table buffered.
>>
>>I have made a list control to display the multiple child records of the current master. It finds the current child records by running a SQL statement like :
>>
>>select * from ChildTable where ChildTable_id in (select ChildTable_id from CrossRefTable where CrossRefTable.MasterTable_id = MasterTable.MasterTable_id) into cursor curSelected
>>
>>I have made a look up screen to be able to pick the multiple child records. It adds the new records into the buffered CrossRefTable.
>>
>>When the SQL runs it appears to run against the 'disk' copy of the CrossRefTable, not the buffered version. I have confirmed this by not buffering the CrossRefTable. In this case everything works as required.
>>
>>Of course I want to buffer the cross table data so the changes can be reverted if required.
>>
>>Can I force the SQL to run against the 'buffered' data. Would I have this problem if I used local views? Do I have to revert to foxpro 2.5 scan ... endscan to build the current ChildTable_id cursor?