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

Retrieve the customer ID (USERID), name (NAME), product code (PUR-CODE), and date of purchase (PUR-DATE) of customers who purchased products on or after September 6, 2011 from the sales history table (SALESLIST) and customer table (USERSLIST).

Table to search

Specification example

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

Retrieval results

Note

• Note that if both tables include columns with the same name, these columns are identified by using a specification in the "table-name"."column-name" format. In this example, the USERID column applies. Therefore, the "SALESLIST"."USERID" and "USERSLIST"."USERID" specifications are used for identification.

• In the FROM clause, specify all the tables to be searched.

• Specify the conditional expression AND "SALESLIST"."USERID"="USERSLIST"."USERID" in order to join the tables based on the value of the customer ID column (USERID) as the key.

To Page Top