R:BASE 11 Help

To add a WHERE Clause to your query, several options are available to link tables, thus applying a WHERE Clause. The act of linking tables connects related information that is stored in the different tables.

 

A linking column is a column in one table that contains the same values as a column in one or more other tables. Linking columns can be two columns with the same name, data type, and size (if TEXT or NUMERIC); or, two columns with different names but compatible data types.

 

Add Common Field Links

The menu option is available to automatically assign the table links based upon the common column names that exist in the tables added to the query.

 

Add PK/FK Field Links

The menu option is available to automatically assign the table links based upon the primary key and foreign key column relationships.

 

Drag and Link

The ability to drag one column name from one table to the linking column in the other table. The link, in the form of a line from column to column, will appear in the workspace joining the tables.

 

Note: When using tables with many columns and a desired field is located at the bottom of the column list, a field may be dragged to the table name (the header), where a dialog will be displayed to select the field from the list. If a common field name match is found, it is selected by default. A filter box is also available to narrow the field list.

 

The direction that you drag the link is relative to create the WHERE clause syntax. In the example below, if EmpID from Employee T1 is dragged to InvoiceHeader T2, the WHERE clause would look like: WHERE T2.EmpID=T1.EmpID. However, if EmpID from InvoiceHeader T2 is dragged to Employee T1, the WHERE clause would look like: WHERE T1.EmpID=T2.EmpID. This drag direction is relevant in cases when the selected columns will limit the results from either table (see Left/Right Outer Joins below).

 

 

 

To change the WHERE clause, you can delete the current link by right-clicking the linking line, and selecting "Unlink" from the popup menu.

 

 

 

To change the properties of the link, right-click the linking line, and select "Link options" from the popup menu. You will then see the following dialog box for altering the WHERE condition.

 

 

 

The Join Operator panel specifies the linking column data relationship between the tables, where the column value is equal (=) to , greater than (>), less than (<), etc. to one another.

 

 

For details on WHERE clauses and operators, see WHERE.

 

The Join Type panel specifies how records are combined from the tables. The join combines the selected columns from the linked tables to form the query. R:BASE adds rows to the query that satisfies the join operator of a column from each of the tables.

 

The important characteristic of the join is the relationship between column values in the two tables. R:BASE compares the values of one column in the first table to the values of another column in the second table based on the operator you specify. The two tables do not need common columns to satisfy the comparison.

 

Depending on your requirements, an Inner join or an Outer join (Left, Right, Full) can be selected. The differences are:

 

•Inner Join - will only return rows when there is at least one row in both tables that match the join condition

•Left Outer Join - will return rows that have data in the left table (Table/Column 1), even if there's no matching rows in the right table

•Right Outer Join - will return rows that have data in the right table (Table/Column 2), even if there's no matching rows in the left table

•Full Outer Join - will return all rows, as long as there's matching data in one of the tables

 

For details on Joins, see OUTER JOIN.

 

When you use an outer join, rows are not required to have matching values. The table order in the FROM clause specifies the left and right table. You can include a WHERE clause and other SELECT clause options such as GROUP BY. The result set is built from the following criteria:

 

•In all types of outer joins, if the same values for the linking columns are found in each table, R:BASE joins the two rows.

•For a left outer join, R:BASE uses each value unique to the left (Table/Column 1) table and completes it with nulls for the columns of the right (Table/Column 2) table when the linking columns do not match.

•A right outer join uses unique values found in the right (Table/Column 2) table and completes the rows with nulls for columns of the left (Table/Column 1) table when the linking columns do not match.

•A full outer join first joins the linking values, followed by a left and right outer join.

 

In most queries an inner join is used, though you will sometimes find it useful to do an outer join. For example, you need an outer join to get all rows in the following scenarios:

 

•When joining a customer table with an orders table to list the customers who ordered something in the current month as well as those who didn't order anything.

•When joining a budget table with an expense table to list each budget item, whether or not there was an expense for that item in the current month.

•When comparing a header (master table) on the "one" side of a one-to-many relationship against a detail (transaction table) on the "many" side to see all the rows of data, whether or not they have associated details.