Left Outer Join

[goc]

Can I join two files, using a Left Outer Join,____I do not have a linking Field to link the tables and want all the records in File A linked to all the recors in File B____So if I have 10 records in File A , and 3 records in File B, I want to end up with 30 records.____I^ve tried this in Version 8 and 9 to no avail.____Regards,____Gerard__

[kfleming]

If you do a user SQL report, you can____SELECT FILEA.FIELDA, FILEB.FIELDB__from __FILEA, FILEB____and get back the cartesian join of these tables.______Kathleen__R&R Support

[goc]

Kathleen,__I dont follow how you do a Sql statement in R&R (Version 8 or 9)

[kfleming]

You use the menu command File->New User SQL____Kathleen__R&R Support

[goc]

Kathleen,____If I go into File... it brings up a screen with Report wizzards,Blank report,Templates etc..____I cant find Sql option anywhere, even in Version 9.____

[kfleming]

Cancel from that menu.__Then select File from the top menu.__In the SQL version, the sub-menu choices will be__New..__New User-SQL...__Open__Close__Data Source Connections__(and several more after this)____you want to select the second choice.__ __Kathleen__R&R Support

[goc]

Is the SQL version different to the Xbase Version 8 or 9____How do I simulate a Left Outer Join in the xBase Version.______To recap.__I have Filea with say 100 records__I hvae fileb with 3 records__There is no field linking them____I want a record set with 100 x 3 records (300)____(I thought I could use the Flexilink by setting up a numeric calculated field in Filea and a numeric calculated field in FileB and using Flexilink to link them.... but it does^nt seem to let me join tables (with Flexlink) with calculated fields ?____Is there a way of dong this______Regards,____Gerard

[kfleming]

To do an Xbase base cartesian join, first create a calculated field with the expression:__1__Use this as your linking field in your relation. Then do a flexlink and in the expression box enter the same expression:__1____Set your relation type to scan and each record in the master file will be matched to each record in the related table.____Kathleen__R&R Support

[goc]

Kathleen, I tried as you suggested setting up a linkinf field of 1 but when I tried to save I get an error message:____Linking & Index Key Fields are different types____

[kfleming]

Make sure that both linking and flexlink expression are the same datatype.__So you could use "1" and "1" or 1 and 1.____Kathleen__R&R Support