Using QMF

Presenting data from more than one table

Sometimes you need information from two different tables. You can accomplish this only if there is a link between the two tables. That is, a column in each table contains identical information. For example, both Q.STAFF and Q.ORG have a column that contains employee numbers. In Q.STAFF this column is ID; in Q.ORG it is MANAGER. With this link, you can combine information from both tables into one report by using the following process:

1. In QMF, enter RESET QUERY to display an empty QBE Query panel.
2. Enter DRAW Q.STAFF.

   
   

   Q.STAFF | ID | NAME | DEPT | JOB | YEARS | SALARY | COMM | --------+----+-------+-------+-------+--------+---------+-------| | | | | | | | |

3. This query uses only the table name and the first two columns, so we can delete the other columns. (See REDUCE command.)

   
   

   Q.STAFF | ID | NAME | --------+--------+-----------| | | |

4. Place the cursor on the command line and enter DRAW Q.ORG.

   
   

   Q.STAFF | ID | NAME | --------+--------+-----------| | | | Q.ORG | DEPTNUMB | DEPTNAME | MANAGER | DIVISION | LOCATION | ------+----------+-----------+---------+------------+----------| | | | | | |

5. Delete the DIVISION and LOCATION columns from the Q.ORG table.

   
   

   Q.STAFF | ID | NAME | --------+--------+-----------| | | | Q.ORG | DEPTNUMB | DEPTNAME | MANAGER | ------+----------+-----------+---------| | | | |

6. Add an unnamed column to the Q.ORG example table and increase its size. (See ENLARGE command.)

   
   

   Q.STAFF | ID | NAME | --------+--------+-----------| | | | Q.ORG | DEPTNUMB | DEPTNAME | MANAGER | | ------+----------+-----------+---------+------------| | | | | |

7. Now add some example elements.

   
   

   Q.STAFF | ID | NAME | --------+--------+-----------| | _ID | _NM | Q.ORG | DEPTNUMB | DEPTNAME | MANAGER | | ------+----------+-----------+---------+------------| P. | | | _ID | _NM |

The same example element (in this case _ID) must be used in two example tables to select only the rows where MANAGER (the manager ID) in Q.ORG is equal to ID in Q.STAFF.

P. can appear in only one table. The example element _NM is added to the unnamed column of the Q.ORG example table so that it is presented from the Q.STAFF table even though no P. appears in the Q.STAFF example table.

This query says the following: Show columns DEPTNUMB, DEPTNAME, and MANAGER from Q.ORG and the NAME column from Q.STAFF. Display the rows where the data in the MANAGER column in Q.ORG is the same as the data in the ID column in Q.STAFF.

Press the Run function key to get this report:

   DEPTNUMB  DEPTNAME        MANAGER  NAME
   --------  --------------  -------  ---------
         20  MID ATLANTIC         10  SANDERS
         38  SOUTH ATLANTIC       30  MARENGHI
         15  NEW ENGLAND          50  HANES
         42  GREAT LAKES         100  PLOTZ
         51  PLAINS              140  FRAYE
         10  HEAD OFFICE         160  MOLINARE
         66  PACIFIC             270  LEA
         84  MOUNTAIN            290  QUILL

See also P. -- Present data in a table.