Left Outer Join

Meeting place for R&R customers and clients to share tips and ideas. Post your questions, messages or problems here.
goc
Posts: 10
Joined: Tue Oct 10, 2017 12:44 pm

Left Outer Join

Post by goc » Mon Jul 22, 2002 12:41 pm

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
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

=> RE: Left Outer Join

Post by kfleming » Mon Jul 22, 2002 1:30 pm

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
Posts: 10
Joined: Tue Oct 10, 2017 12:44 pm

==> RE: Left Outer Join

Post by goc » Mon Jul 22, 2002 3:29 pm

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

kfleming
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

===> RE: Left Outer Join

Post by kfleming » Mon Jul 22, 2002 4:09 pm

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

goc
Posts: 10
Joined: Tue Oct 10, 2017 12:44 pm

===> RE: Left Outer Join

Post by goc » Wed Jul 24, 2002 7:49 am

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
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

====> RE: Left Outer Join

Post by kfleming » Wed Jul 24, 2002 11:33 am

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
Posts: 10
Joined: Tue Oct 10, 2017 12:44 pm

=====> RE: Left Outer Join

Post by goc » Wed Jul 24, 2002 4:39 pm

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
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

======> RE: Left Outer Join

Post by kfleming » Wed Jul 24, 2002 8:12 pm

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
Posts: 10
Joined: Tue Oct 10, 2017 12:44 pm

=======> RE: Left Outer Join

Post by goc » Thu Jul 25, 2002 3:41 pm

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
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

========> RE: Left Outer Join

Post by kfleming » Fri Jul 26, 2002 6:12 am

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

Post Reply