This example uses the Q.SUPPLIER, Q.PARTS, and Q.PROJECT tables to create a query that shows the supplier name, the part name, the project number, and the start date for all the parts used by each project in the Q.PROJECT table.
If you look at the sample table in Appendix B, QMF Sample Tables, you see the supplier name is in Q.SUPPLIER as COMPANY, the part name is in Q.PARTS as PARTNAME, and the project number is in Q.PROJECT as PROJNO. To get all the information for the report, you need to join these three tables.
To join multiple tables, you join two tables at a time by a common column. In this example, the ACCTNO column in Q.SUPPLIER and the SUPPNO column in Q.PARTS contain the same information, so you can join those two tables. Likewise, the PRODNUM column in Q.PARTS and the PRODNO column in Q.PROJECT contain the same information, so you can also join those tables.
To join multiple tables:
The Join Tables panel displays. The first table you selected appears under the heading Joined Tables. Select the second table you want to join from the list under the heading Tables.
Figure 45. The Join Tables panel
+--------------------------------------------------------------------------------+ | PROMPTED QUERY MODIFIED LINE 1 | | +-------------------------------------+ | | Tables: | Tables | | | +-----------------------------------------------------------------------------+| | | Join Tables || | | || | | Select two tables that you want to join; one from each group. || | | || | | Joined Tables Tables || | | ------------- ------ || | | 1 to 3 of 3 || | | 1 1. Q.SUPPLIER 2 * Q.SUPPLIER || | | 2. Q.PARTS || | | 3. Q.PROJECT || | +-----------------------------------------------------------------------------+| | | F1=Help F5=Describe F7=Backward F8=Forward F12=Cancel || | +-----------------------------------------------------------------------------+| | | | | | | | | | | +--------------------------------------------------------------------------------+
Figure 46. The Join Columns panel
+--------------------------------------------------------------------------------+ | PROMPTED QUERY MODIFIED LINE 1 | | +-------------------------------------+ | | Tables: | Tables | | | +-----------------------------------------------------------------------------+| | | Join Tables || | | +----------------------------------------------------------------+ || | | Select t | Join Columns | || | | | | || | | Joined | Select a column from each table. Rows that have equal | || | | ------ | values in those columns will be joined. | || | | | | || | | 1 1. Q.S | Q.SUPPLIER Q.PARTS | || | | | 1 to 6 of 7 | || | | | 1. ACCTNO 1. SUPPNO | || | +--------- | 2. COMPANY 2. PARTNAME | +| | | F1=Help | 3. STREET 3. PRODUCT | || | +--------- | 4. CITY 4. PRODNO | +| | | 5. STATE 5. PROJNO | | | | 6. ZIP | | | +----------------------------------------------------------------+ | | | F1=Help F5=Describe F7=Backward F8=Forward F12=Cancel | | | +----------------------------------------------------------------+ | +--------------------------------------------------------------------------------+
You have joined the first two tables. The Join Tables panel displays again. This time, both tables you have joined appear under the heading Joined Tables. You select the last table to join with the other two from the list under the heading Tables. For this example, select Q.PARTS from the list under Joined Tables. Select Q.PROJECT from the list under Tables.
Figure 47. The Join Tables panel displays the tables you already joined.
+--------------------------------------------------------------------------------+ | PROMPTED QUERY MODIFIED LINE 1 | | +-------------------------------------+ | | Tables: | Tables | | | +-----------------------------------------------------------------------------+| | | Join Tables || | | || | | Select two tables that you want to join; one from each group. || | | || | | Joined Tables Tables || | | ------------- ------ || | | 1 to 3 of 3 || | | 2 1. Q.SUPPLIER 3 1. Q.SUPPLIER || | | 2. Q.PARTS 2. Q.PARTS || | | 3. Q.PROJECT || | +-----------------------------------------------------------------------------+| | | F1=Help F5=Describe F7=Backward F8=Forward F12=Cancel || | +-----------------------------------------------------------------------------+| | | | | | | +--------------------------------------------------------------------------------+
The Join Columns panel displays again.
Figure 48. QMF displays columns from the second set of tables.
+--------------------------------------------------------------------------------+ | PROMPTED QUERY MODIFIED LINE 1 | | +-------------------------------------+ | | Tables: | Tables | | | +-----------------------------------------------------------------------------+| | | Join Tables || | | +----------------------------------------------------------------+ || | | Select t | Join Columns | || | | | | || | | Joined | Select a column from each table. Rows that have equal | || | | ------ | values in those columns will be joined. | || | | | | || | | 1 1. Q.S | Q.PARTS Q.PROJECT | || | | 2. Q.P | 1 to 6 of 7 | || | | | 4 1. SUPPNO 2 1. PROJNO | || | +--------- | 2. PARTNAME 2. PRODNUM | +| | | F1=Help | 3. PRODUCT 3. DEPT | || | +--------- | 4. PRODNO 4. STARTD | +| | | 5. PROJNO 5. ENDD | | | | 6. TIMESTAMP | | | +----------------------------------------------------------------+ | | | F1=Help F5=Describe F7=Backward F8=Forward F12=Cancel | | | +----------------------------------------------------------------+ | +--------------------------------------------------------------------------------+
You have joined the second and third tables.
If you have more tables to join, the Join Tables panel displays. If you have joined all your tables, the Specify panel displays, so you can select the columns to display on the report.