Lesson 5: Retrieving more complex data with table joins

Up to this point, each data access function has retrieved rows from only one table. In this lesson, you learn to use SQL table joins with EGL to work with multiple tables at once.

A full discussion of SQL table joins is beyond the scope of this tutorial. In short, a table join assembles a result set from two or more related tables.

For example, the CUSTOMERS table in the database includes a customer ID number as a primary key, along with the first and last names of the customers (as well as several other columns):

Table 1. Sample data from the CUSTOMER table
CUSTOMER_ID	FIRST_NAME	LAST_NAME
1	Fred	Filibuster
2	Andy	Lundquist
3	Billie	Kingman

The ORDERS table includes an order ID number as a primary key, and the amount of the order. The table also includes the ID number of the customer who placed the order, making that column a foreign key:

Table 2. Sample data from the ORDERS table
ORDER_ID	CUSTOMER_ID	ORDER_AMOUNT
1	1	111.11
2	1	222.22
3	1	333.33

In this case, the CUSTOMER_ID column is a good candidate for a table join because this column creates a relationship between the two tables. In this lesson, you will join these two tables with a customized SQLRecord part, producing a result set that matches the customers with the orders placed by those customers.

Table joins are a complex database operation, so you should be familiar with the behavior of the database and test your output carefully. As you'll see in this lesson, you don't always get the data you may expect.

Create a new EGL source file to hold a new SQLRecord part:
1. Right-click the EGLSQL project in the Project Explorer view and then click New > EGL Source File.
2. In the New EGL Source File window, type the name data in the Package field.
3. In the EGL Source File Name field, type records.
4. Click Finish.
The new EGL source file is created and opens in the editor.
In the new file, below the package statement, type in the name of a new SQLRecord to combine both the CUSTOMER and ORDERS tables:
```
record OrderCustomerJoin type SQLRecord
  {tableNames = [["EGL.CUSTOMER", "C"], ["EGL.ORDERS", "O"]]}
end
```
The first line of this record is the same as the other SQLRecord parts: it specifies a name for the record and the SQLRecord stereotype. The second line specifies the tables to which this record relates, as values of the tableNames property. The single-table records you have worked with in previous lessons listed only one table in this property, such as the ORDERS table for the Orders record:
```
record Orders type sqlRecord { 
  tablenames=[["EGL.ORDERS"]]
...
```
The OrderCustomerJoin record you are creating must list two tables in the tableNames property to retrieve data from both tables. For clarity, the record includes an alias for each table name ("O" for ORDERS and "C" for CUSTOMERS). These aliases make it easier to refer to the table names in the record, as you will see soon.
Now that you have specified the basic information for the Record part, EGL can fill in the rest. However, you must first specify which database EGL will retrieve connection information from.
Set the Derby database as the default SQL database for EGL:
1. Click Window > Preferences.
2. In the Preferences window, expand EGL and click SQL Database Connections.
3. In the Connections list, select your database connection, named EGLDerbyR7 or EGLDerbyR71.
4. Click OK.
Back in the records.egl file, place the cursor on the name of the record.
With the cursor on the name of the OrderCustomerJoin record, right-click and then click SQL Record > Retrieve SQL. The EGL SQL Retrieve feature

In the password prompt, leave the default values for your user name and password. The sample database does not require a particular user name or password. The EGL SQL Retrieve feature creates the rest of the SQLRecord part for you, including fields for the rows in the tables and the keyItems property:

record OrderCustomerJoin type SQLRecord
  {tableNames = [["EGL.CUSTOMER", "C"], ["EGL.ORDERS", "O"]], 
  keyItems=[CUSTOMER_ID, ORDER_ID]}

  CUSTOMER_ID int
    {column="C.CUSTOMER_ID", isReadOnly=yes};
  FIRST_NAME string
    {column="C.FIRST_NAME", isReadOnly=yes, 
      isSqlNullable=yes, sqlVariableLen=yes, maxLen=30};
  LAST_NAME string
    {column="C.LAST_NAME", isReadOnly=yes, 
      isSqlNullable=yes, sqlVariableLen=yes, maxLen=30};
...
  ORDER_ID int
    {column="O.ORDER_ID", isReadOnly=yes};
  orders_CUSTOMER_ID int
    {column="O.CUSTOMER_ID", isReadOnly=yes, 
      isSqlNullable=yes};
  ORDER_AMOUNT decimal(8,2)
    {column="O.ORDER_AMOUNT", isReadOnly=yes, 
      isSqlNullable=yes};
  ORDER_DETAILS string
    {column="O.ORDER_DETAILS", isReadOnly=yes, 
      isSqlNullable=yes, sqlVariableLen=yes, maxLen=111};
end

Note that the value of the column property for each field begins with the table's alias as specified in the tableNames property. This alias can help you keep track of which table a particular field relates to.

Save the records.egl file, but keep it open.
Create a new Program part in the programs package named printCustomerOrders.
In the new program, remove the default code.
In the program's main function, create an array variable based on the OrderCustomerJoin record that you just created:
```
allCustomerOrders OrderCustomerJoin[0];
```
Remember to use content assist to insert the Record part type by pressing Ctrl+Space. If you do not use content assist, you must manually add an import statement to the top of the file, immediately below the package statement:
```
import data.OrderCustomerJoin;
```

Use the open and forEach statements to retrieve the rows from the database and print the values to the console:

tempRec OrderCustomerJoin;
open resultSet for tempRec;
forEach (tempRec)
  SysLib.writeStdout(tempRec.CUSTOMER_ID :: " " :: 
    tempRec.LAST_NAME :: " " :: tempRec.ORDER_ID);
end

Save, generate, and run the program. If you're familiar with the pitfalls of table joins, you may have noticed the mistake in this table join and why the output is not very useful:
```
1 Filibuster 1
1 Filibuster 2
1 Filibuster 3
...
1 Filibuster 22
1 Filibuster 23
2 Lundquist 1
2 Lundquist 2
2 Lundquist 3
2 Lundquist 4
...
2 Lundquist 22
2 Lundquist 23
3 Kingman 1
3 Kingman 2
...
```
The database has not made a logical cross-reference between the tables; instead, it has paired each row in the CUSTOMERS table with each row in the ORDERS table in every possible combination. In these results, each customer is recorded as making every order, which is obviously incorrect. If you make the SQL code behind the open statement explicit, you can see that the SQL is selecting every row from both tables without using a WHERE clause:
```
open resultSet for tempRec with
  #sql{
    select
      C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, C.PASSWORD, 
      C.PHONE, C.EMAIL_ADDRESS, C.STREET, C.APARTMENT, 
      C.CITY, C.STATE, C.POSTALCODE, C.DIRECTIONS, 
      O.ORDER_ID, O.CUSTOMER_ID, O.ORDER_AMOUNT, 
      O.ORDER_DETAILS, O.ORDER_DATE, O.ORDER_STATUS
    from EGL.CUSTOMER C, EGL.ORDERS O
    order by
      C.CUSTOMER_ID, O.ORDER_ID asc
  };
```
To join the tables meaningfully, you must tell the database how to merge the tables logically, instead of returning the union of the tables. You could do this by editing the explicit SQL code, but in this case you would have to do this every time you use the record, or else you risk retrieving inaccurate data. Instead, you will set the default selection condition for this record so the implicit SQL code will always preform a meaningful table join.
If you made the SQL code behind the open statement explicit, make it implicit again by placing the cursor on the record name, right-clicking, and then clicking SQL Statement > Remove.
Return to the Record part definition in the records.egl file.
Add the defaultSelectCondition property to the Record part definition:
```
record OrderCustomerJoin type SQLRecord
  {tableNames = [["EGL.CUSTOMER", "C"], ["EGL.ORDERS", "O"]], 
  keyItems=[CUSTOMER_ID, ORDER_ID],
  defaultSelectCondition = #sqlCondition{ condition }}
```
The defaultSelectCondition property specifies the WHERE clause in the implicit SQL code and default explicit SQL code when you use get or open with this record.
Inside the #sqlCondition{} block, replacing the default "condition" text, enter the correct WHERE clause for an SQL table join between these two tables, taking care to use the table aliases instead of the full table names:
```
defaultSelectCondition = #sqlCondition{ O.CUSTOMER_ID = C.CUSTOMER_ID }}
```
You do not need to add the keyword WHERE; EGL inserts it automatically in the SQL code in this case.

Save the file. Now if you make the SQL code behind the open statement explicit, it looks like this:

open resultSet for tempRec with
  #sql{
    select
      C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, C.PASSWORD, 
      C.PHONE, C.EMAIL_ADDRESS, C.STREET, C.APARTMENT, 
      C.CITY, C.STATE, C.POSTALCODE, C.DIRECTIONS, 
      O.ORDER_ID, O.CUSTOMER_ID, O.ORDER_AMOUNT, 
      O.ORDER_DETAILS, O.ORDER_DATE, O.ORDER_STATUS
    from EGL.CUSTOMER C, EGL.ORDERS O
    where
      O.CUSTOMER_ID = C.CUSTOMER_ID
    order by
      C.CUSTOMER_ID, O.ORDER_ID asc
  };

Now the SQL code includes a WHERE clause that will limit the rows returned to those that have the same customer number in both the CUSTOMER and ORDERS table.

Save, generate, and run the program.

With a meaningful table join, the results of this program show which customer made each order:

1 Filibuster 1
1 Filibuster 2
1 Filibuster 3
...
1 Filibuster 11
1 Filibuster 18
2 Lundquist 7
2 Lundquist 8
2 Lundquist 12
2 Lundquist 15
2 Lundquist 20
3 Kingman 13
4 Liebowitz 14
6 Springsteen 22
7 St. Louis 16
8 Sharov 17
9 Hudak 23

Here is the complete code of the two files used in this lesson. If you see any errors marked by red X symbols in either file, make sure your code matches this code:Completed printCustomerOrders.egl and records.egl files after lesson 5.