1.9 Retrieving data with multiple tables specified (table join)

If the data to be retrieved is distributed across multiple tables, perform the retrieval by associating columns that contain the same information. This is called a table join. As an example, we describe a table join of the sales history table (SALESLIST) and customer table (USERSLIST).

Example:

The following retrieves the name (NAME) of customers who purchased a product on September 7, 2011 from the sales history table (SALESLIST) and customer table (USERSLIST).

SELECT statement specification

  SELECT "NAME"
      FROM "SALESLIST","USERSLIST"
          WHERE "PUR-DATE"=DATE'2011-09-07'
          AND "SALESLIST"."USERID"="USERSLIST"."USERID"

Description

The date of purchase (PUR-DATE) information specified in the search condition is located in the sales history table (SALESLIST), while the name (NAME) information to be output as the retrieval result is located in the customer table (USERSLIST). In this case, we join the SALESLIST and USERSLIST tables using the customer ID column (USERID), which is common to both tables.

Retrieval results

Organization of this section

• 1.9.1 Example 1: Retrieve customer purchases from the customer table and sales history table (1 of 3)

• 1.9.2 Example 2: Retrieve customer purchases from the customer table and sales history table (2 of 3)

• 1.9.3 Example 3: Retrieve customer purchases from the customer table and sales history table (3 of 3)

To Page Top