Using QMF

Joining multiple columns

You might need to join tables on more than one column. For example, if you join the Q.PARTS table and the Q.PROJECT table on their PRODNO and PRODNUM columns alone, you might get a report showing some incorrect project numbers for the products. That is because the sample tables have more than one project number for some products. You need to join the Q.PARTS and Q.PROJECTS tables at both of the columns that share the same type of information. You must join the tables on the PROJNO columns as well as the PRODNO and PRODNUM columns.

To join multiple columns:

1. Create or display a query that joins two tables at a single column. For this example, display a query that joins the Q.PARTS and Q.PROJECT tables.
2. Press the Cancel function key to remove the Specify panel from the Prompted Query panel.
3. Move the cursor to the last pair of joined columns under the Join Tables heading.
4. Press the Insert function key. The Join Tables panel displays.
5. Select the tables you want to join on an additional column. For this example, select the Q.PARTS and the Q.PROJECT tables again. The Join Columns panel displays.
6. Select the columns you want to join. For this example, select the PROJNO columns from both tables.

   The query displays with the additional columns you joined.

   Figure 49. QMF shows that the two tables are joined at a second column.
   

   +--------------------------------------------------------------------------------+
   | PROMPTED QUERY                           MODIFIED     LINE     1               |
   |                                                                                |
   |   Tables:                                                                      |
   |  _     Q.PARTS(B)                                                              |
   |  _     Q.PROJECT(C)                                                            |
   |                                                                                |
   |   Join Tables:                                                                 |
   |  _     A.ACCTNO And B.SUPPNO                                                   |
   |  _     And B.PRODNO And C.PRODNUM                                              |
   |  _     And B.PROJNO And C.PROJNO                                               |
   |                                                                                |
   |   Columns:                                                                     |
   |  _     PARTNAME                                                                |
   |  _     C.PROJNO                                                                |
   |  _     STARTD                                                                  |
   |                                                                                |
   |   *** END ***                                                                  |
   |                                                                                |
   | 1=Help       2=Run        3=End      4=Show SQL  5=Change        6=Specify     |
   | 7=Backward   8=Forward    9=Form     10=Insert   11=Delete       12=Report     |
   | OK, ENTER performed. Please proceed.                                           |
   | COMMAND ===>                                     SCROLL ===> PAGE              |
   +--------------------------------------------------------------------------------+