When using an Outer Join, rows are not required to have matching values. The table order in the FROM clause specifies the left and right table. For a Full Outer Join, R:BASE returns all rows, as long as there's matching data in one of the tables.
The following instructions will step through the process to recreate a Full Outer Join in the Query Builder.
To continue, launch R:BASE 11 and connect to the RRBYW20 sample database. The database is located in the following default installation directory: "C:\RBTI\RBG11\Samples\RRBYW20".
1.Start R:BASE
2.Connect to the RRBYW20 sample database, by selecting "Database" > "Connect", and navigating to the above default installation directory based upon your version.
3.Then, launch the Query Builder by selecting "Tools" > "Query by Example" from the main menu bar
In the Query Builder you will see a list of tables and views within a panel to the left that can be added to your query. The list contains all tables and views for the connected database.
4.Add the "Customer" table to the query by selecting the table, and then selecting the "Add Table/View" button.
The table should now be listed under "Tables/Views In Use".
5.Right click on the "Customer" table under "Tables/Views In Use", and choose "Join Properties..." from the speed menu.
The "Join Properties" dialog will be displayed.
6.From the "Join Type" radio button options, select "Full Outer"
Take note that the other options within the window will become enabled. In this window you will select the linking columns for the join, the second table for the join, and the join operation. As alias can also be assigned to the linking column.
7.From the "Left Column" panel, choose "T1.CustID" from the "Column Name:" drop down box. The "T1" alias may vary from your R:BASE screen.
8.From the "Right Table/Column" panel, choose "Contact" from the "Table Name:" drop down box.
9.From the "Right Table/Column" panel, enter "T2" into the "Alias:" field to assign the table alias for the join. If "T2" is already used for the "Customer" table, then use "T3" as the alias.
10.Again in the "Right Table/Column" panel, choose the "CustID" column from the "Column Name:" drop down box. The alias you have assigned will appear in from of the column name.
11.From the "Join Operation" radio button options, choose the equal character (=).
Your end result for the Join Properties should look like, or close to, the following:
12.Select the "OK" button.
In the Query Builder main window, you should see the SQL syntax displayed in the bottom of the page as:
SELECT *
FROM Customer T1 FULL OUTER JOIN Contact t2 ON T1.CustID = t2.CustID
13.Right click on the "Customer" table under "Tables/Views In Use", and choose "Select Columns..." from the speed menu.
A dialog will be displayed with the available columns for each of the tables.
Once the "Tables" dialog is displayed, you can add the columns one at a time by selecting the desired column and pressing the "Add" button, or double click on a desired column. All columns can be added at once by pressing the "Add All" button.
14.Select the "Company" column, and select the "Add" button.
When a column is added, an optional column alias can be assigned in the "Column Alias" dialog, that appears each time a column is added to the query/view.
15.Press the "OK" or "Cancel" buttons to allow R:BASE to assign the alias for you.
16.From the "Contacts" table, add the "ContFName" and "ContLName" columns individually, and do not enter an alias for each.
17.Press the "OK" button to save the columns added.
In the Query Builder main window, you should see the SQL syntax displayed in the bottom of the page as:
SELECT T2.Company,T3.ContFName,T3.ContLName
FROM Customer T2 FULL OUTER JOIN Contact T3 ON T2.CustID = T3.CustID
18.Now, browse the query results by selecting "Query" > "Browse Query" from the main menu bar. The results should look like the following:
The results show the Contact table added to the Customer table. Notice that where any Contact matches for an existing Customer, the company is listed. For example, since there are three contacts for the "Computer Warehouse - II" the company name is listed for each contact.
If you wish to save the query, follow the below steps.
1.From the main menu bar, select "File" > "Save Query As View..."
2.In the dialog, enter "CustContacts" within the "View Name:" field.
3.In the dialog, enter "Customer and Contact List" within the "View Comment:" field.
4.Select the "OK" button.
5.Close the Query Builder by selecting "File" > "Close" from the menu bar.
In the Database Explorer, the view will be displayed with the name, comment, and number of columns.
