9. Joining Tables

In one of the previous chapters, you have learned how to add a list of orders to the table of CDs. The example was not very realistic because the orders contained only the customer’s name while other details were missing, and because the same data of a particular customer would have to be entered multiple times if the customer ordered more than one album.
It is therefore more convenient to create a separate table which contains one record per customer identified by a numeric code. You can then use only this numeric code in the list of orders to avoid storing duplicate information about the same customer.
First, create a table of customers:
Double-click the new data item in the Data document section.
Name the new data item customers.
Switch the data type to Table, sequence, and insert the following columns (applicable data types are enclosed in parentheses): last_name (String), first_name (String), address (String), city (String), state (String), zip_code (String), phone (Integer number), and id (Integer number). Column id will contain a unique identifier used to reference the customer.

Generate Identifiers Automatically

This step is intended for advanced users. It enables you to assign values to the id column automatically. You can skip this step and enter a value manually for every new customer. There are two ways of defining an automatic identifier assignment. Either:
Create an item of the Integer number type in the Data document section, and name it (for example) c_id_gen (generator of customer identifiers); this data item will contain the identifier assigned to the last customer.
When creating the customers table, set the language of the Inserter function in the Table properties section to Enki, and specify the following expression:
id = ++document.data.c_id_gen
This function is evaluated whenever you add a new row into the table. The expression increases the value of the c_id_gen item by one (++ operator) and assigns it to the id column of the new row.
If the table already exists, you can make the same modification through the customers item in the Data Type document section.
Set the Inserter function to:
id = (for(&p = up(this), [Int]0) max(@p, p.id)) + 1
This expression finds the highest identifier already used in existing records and assigns a value incremented by one to the new record. See the for command of the Enki language. The this identifier refers to the row that is being inserted and the up(this) expression refers to the table into which the row has just been inserted.
The advantage of this approach is that you need not create an additional data item holding the last used identifier.
A detailed explanation can be found in the Sequence, Table chapter of the Reference Guide.

Modify the Base Table

Now add the c_id id column (customer identifier) to the list of customers and remove the name column because it is no longer needed:
Right-click the name column (you must click one of the nested table columns) and select New Column from the context menu. Set the Column insertion level to Data, Name to c_id, and Data type to Integer number.
Display the context menu for the name column once again and select Delete Column. In the dialog that pops up, select the from data option and press OK to remove the column.
It is useful to keep the customers table open while entering data into the base table – this is achieved either by double-clicking the customers item in the left pane of the document main window or by selecting Open in Window from the context menu of this item.

Join the Tables

Customers in the list of orders are now defined only by their numeric identifiers. This arrangement is not very transparent because you probably need to see customer details for every order. This can be achieved by joining information from the customers table with the list of orders – every row in the table of orders will be enriched by details from the customers table. Use file Examples\Chapter 09.s-d to try out joining of two tables. Follow these steps:
Right-click the c_id column under the order column and select Join Table from the context menu.
In the dialog that pops up, leave the Object name under the Table to be joined heading set to document, and in the Data reference field, select data.customers from the listbox. New items will appear.
In table Relation between the base and joined table, specify that the value of the id column from the joined table (customers) must be equal to the c_id column from the base table. In other words, select id from the listbox in the Joined column and c_id from the listbox in the Base column. Leave the “=” operation in place in the middle column.
Check the ungroup joined table option. In general the relation between the columns of the base and joined table as defined above may result in a situation where one row from the base table corresponds to multiple rows (or now rows at all) from the joined table. The ungroup joined table option should not be checked in such situation – all matching rows of the joined table will be collected into a nested table. However, in our example, there is always exactly one matching row in the joined table, and therefore it makes no sense to create a nested table that would always contain only one record. If this table is ungrouped, the resulting effect is the simple addition of new items to the existing ones.
Press OK to join the table. The result will look like this:
Now if you insert a new record into the table of orders, and specify the customer id, the remaining details of that customer will automatically appear as well. You should keep in mind that joining a table does not affect the physical data of the base table, but only its view.
It would be more convenient to select the customer id from a list rather than entering it manually. This task will be discussed in chapter 12.
A document containing a modified table populated with data is stored in file Examples\Chapter 09a.s-d.
Note: the table filter has been modified to produce the table join. An injudicious change of the filter of a table joined with another table may violate the relation between the columns of the base and joined table and may therefore change the links between the rows of the joined and base table.

Further Information

The following chapters of the Reference Guide contain detailed information on the discussed topic:
Enki Language – language used for the specification of expressions