Joining multiple columns

In this example, you join two tables on two columns because joining on only one column might produce incorrect information.

About this task

If you join the QMF sample tables Q.PARTS and Q.PROJECT on their PRODNO and PRODNUM columns alone, you might get a report that shows incorrect project numbers for the products. The sample tables have more than one project number for some products. Join the Q.PARTS and Q.PROJECT 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.

Procedure

To join multiple columns:

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.
Press the Cancel function key to remove the Specify panel from the Prompted Query panel.
Move the cursor to the last pair of joined columns under the Join Tables heading.
Press the Insert function key. The Join Tables panel displays.
Select the tables that 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.

Select the columns that you want to join.

For this example, select the PROJNO columns from both tables.

The query displays with the additional columns you joined, as shown in the following figure.

Figure 1. 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