>I have a situation in which I need to join 3 tables with an sql statement. I am trying to create a view and although I got it to work from the command line by splitting it into two statements I would like to generate a view that can be populated at runtime.
>
>The object is to display a grid with a description and a check box indicating that the record exists in the data file.
>
>The first table is a contact table with a contact id.
>
>The second table is a reference table with type, code and code description fields.
>
>I want an outer join to create a table with all contacts and all roles.
>
>I then want to add a logical field to indicate that the record (contact id and code description) appear in a data file. (BTW, I broke down and added a logical field here (INFILE) that is always true when the record is written.
>
>I can get it to work if I:
>
>create sql view tmpfile as sele people.contid, genref.codedesc from people, genref where genref.codetype = 'ROL'
>
>then;
>
>create cursor view descvue as sele (iif(rolefile.infile) = .t., .t., .f.)) as infile, tmpfile.contid, tmpfile.codedesc from rolefile, tmpfile where rolefile.contid = tmpfile.contid and rolefile.codedesc = tmpfile.codedesc
>
>BUT, when I try to store this to the database I get an error that says that the file TMPFILE doesn't exist and prompts me for a table.
>
>Any ideas?

To get a logical field in a cursor that indicates if one of the keys exists in another table, open the datafile table before the view is opened and define the view as something like:

SELE Mainfile.Code, Lookupfile.Descrip, IIF(SEEK(MainFile.Pk, "DataFile", "FK"), .T.,.F.) AS InFile FROM MainFile, LookupFile WHERE Mainfile.Pk = Lookupfile.FK