Retrieving Data From Multiple Tables

All the queries looked at so far have been answered by accessing data from one table. Sometimes, however, answers to a query may require data from two or more tables.

For example, for the Corporate Librarian to display a list of contributing authors with their long form department name requires data from the REPORT table (author) and data from the DEPARTMENT table (department name). Obtaining the data you need requires the ability to combine two or more tables. This process is commonly referred to as "joining the tables".

Two or more tables can be combined to form a single table by using the join operation. The join operation is based on the premise that there is a logical association between two tables based on a common attribute that links the tables. Therefore, there must be a common column in each table for a join operation to be executed. For example, both the REPORT table and the DEPARTMENT table have the department identification code in common. Thus, they can be joined.

Joining two tables in Texis is accomplished by using a SELECT statement. The general form of the SELECT statement when a join operation is involved is:

SELECT   column-name1 [,column-name2] ...
     FROM     table-name1, table-name2
     WHERE    table-name1.column-name = table-name2.column-name ;

The combination of table name with column name as stated in the WHERE clause describes the Join condition.