The data that you need for a report could be located in more than one table. In order to select the data from the tables, you join the tables in a query. Joining tables enables you to select data from multiple tables as if the data were contained in one table. Joins do not alter the original tables.
The SQL Query Window supports two types of joins:
Inner Joins return a result table for all the rows in a table that have one or more matching rows in the other table or tables that are listed in the Selected Tables list.
Outer Joins are inner joins that are augmented with rows that do not match any row from the other table in the join. See "Creating and Using Outer Joins" on page 66 for more information about outer joins.
For this example, you use an inner join to display the hourly wage for each employee identification number.
In the previous example, you added SAMPLE.WAGE to the Available Tables list. Select SAMPLE.SALARY and SAMPLE.WAGE from the Available Tables list and add them to the Selected Tables list. Select OK to display the SQL QUERY COLUMNS window.
Select Identification Number , JOBCODE , and Hourly Rate from the Available Columns list and move them to the Selected Columns list.
Select
View – Join Type
to display the Join Types window.
Select Matched Join and OK .
In the Columns for Setting Join Criteria window, select Salary from both the SAMPLE.SALARY Columns list and the SAMPLE.WAGE Columns list. Select JOBCODE from the SAMPLE.SALARY Columns list and select Job Code from the SAMPLE.WAGE Columns list.
Select OK to return to the SQL QUERY COLUMNS window.
To run your query and view the output in the Output window, select
Tools – Run Query – Run Immediate