Merging data from multiple tables into a single column

You can merge data from two or more tables into a single column on a report by using the keyword UNION.

Procedure

Create two or more queries to select the data you want to merge, then specify the keyword UNION between the queries.

In the following figure, the first query selects the department name and number from the Q.ORG table and creates a column that displays the words WAITING FOR WORK. The second query selects the department name and number from the Q.PROJECT and Q.ORG tables, and creates a column that displays the words HAS WORK. The database determines the name of the new column, unless you change it using QMF forms.

Select the same number of columns for each query. Corresponding columns must be the same general data type. Corresponding columns must all either allow null values or not allow null values. If you want to order the columns, specify a column number because the names of the columns you are merging are probably different. If you want to display duplicate rows on the report, specify UNION ALL instead of UNION.

Figure 1. This SQL query merges data from two columns into one.

SELECT DEPTNUMB, DEPTNAME, 'WAITING FOR WORK'
    FROM Q.ORG
    WHERE DEPTNUMB NOT IN (SELECT DEPT FROM Q.PROJECT)
UNION
SELECT O.DEPTNUMB, O.DEPTNAME, 'HAS WORK'
    FROM Q.PROJECT P, Q.ORG O
    WHERE P.DEPT = O.DEPTNUMB
ORDER BY 1

QMF displays the report that is shown in the following figure when you run the query, showing the department names and numbers and their status information on the same report.

Figure 2. The report shows the two new columns merged into one.

   DEPTNUMB  DEPTNAME        COL1
   --------  --------------  ---------------- 
         10  HEAD OFFICE     HAS WORK
         15  NEW ENGLAND     HAS WORK
         20  MID ATLANTIC    HAS WORK
         38  SOUTH ATLANTIC  HAS WORK
         42  GREAT LAKES     HAS WORK
         51  PLAINS          HAS WORK
         66  PACIFIC         HAS WORK
         84  MOUNTAIN        WAITING FOR WORK

You can specify the order in which you want to merge the columns from multiple tables. Specifying order is important when you use the UNION and UNION ALL keywords. Use parentheses to indicate the columns or which tables you want merged first. The conditions inside the parentheses are checked before the conditions outside the parentheses.

Example

For example, this query produces Report A in the following figure:

 (SELECT ID, NAME, SALARY
     FROM Q.STAFF
     WHERE SALARY>12000
 UNION ALL
 SELECT ID, NAME, SALARY
     FROM Q.STAFF
     WHERE DEPT=38)
 UNION
 SELECT ID, NAME, SALARY
     FROM Q.STAFF
     WHERE JOB='SALES'

If you move the parentheses, the same query produces Report B in the following figure:

 SELECT ID, NAME, SALARY
     FROM Q.STAFF
     WHERE SALARY>12000
 UNION ALL
 (SELECT ID, NAME, SALARY
     FROM Q.STAFF
     WHERE DEPT=38
 UNION
 SELECT ID, NAME, SALARY
     FROM Q.STAFF
     WHERE JOB='SALES')

Figure 3. The two reports show the differences in merging order.

            REPORT A                             REPORT B
      ID  NAME           SALARY            ID  NAME           SALARY
  ------  ---------  ----------        ------  ---------  ----------
      10  SANDERS      18357.50            20  PERNAL       18171.25
      20  PERNAL       18171.25            30  MARENGHI     17506.75
      30  MARENGHI     17506.75            40  O'BRIEN      18006.00
      40  O'BRIEN      18006.00            60  QUIGLEY      16808.30
      50  HANES        20659.80            70  ROTHMAN      16502.83
      60  QUIGLEY      16808.30            90  KOONITZ      18001.75
      70  ROTHMAN      16502.83           120  NAUGHTON     12954.75
      80  JAMES        13504.60           150  WILLIAMS     19456.50
      90  KOONITZ      18001.75           180  ABRAHAMS     12009.75
     100  PLOTZ        18352.80           220  SMITH        17654.50
     110  NGAN         12508.20           280  WILSON       18674.50
     120  NAUGHTON     12954.75           300  DAVIS        15454.50
     140  FRAYE        21150.00           310  GRAHAM       21000.00
     150  WILLIAMS     19456.50           320  GONZALES     16858.20
     160  MOLINARE     22959.20           340  EDWARDS      17844.00
     170  KERMISCH     12258.50            10  SANDERS      18357.50
     180  ABRAHAMS     12009.75            20  PERNAL       18171.25
     190  SNEIDER      14252.75            30  MARENGHI     17506.75
     210  LU           20010.00            40  O'BRIEN      18006.00
     220  SMITH        17654.50            50  HANES        20659.80
     230  LUNDQUIST    13369.80            60  QUIGLEY      16808.30
     240  DANIELS      19260.25            70  ROTHMAN      16502.83
     250  WHEELER      14460.00            80  JAMES        13504.60
     260  JONES        21234.00            90  KOONITZ      18001.75
     270  LEA          18555.50           100  PLOTZ        18352.80
     280  WILSON       18674.50           110  NGAN         12508.20
     290  QUILL        19818.00           120  NAUGHTON     12954.75
     300  DAVIS        15454.50           140  FRAYE        21150.00
     310  GRAHAM       21000.00           150  WILLIAMS     19456.50
     320  GONZALES     16858.20           160  MOLINARE     22959.20
     340  EDWARDS      17844.00           170  KERMISCH     12258.50
     350  GAFNEY       13030.50           180  ABRAHAMS     12009.75

The first query selects employees whose salaries are greater than $12,000.00 and all employees from Department 38. Then, it eliminates any duplicate entries by selecting only employees who work in Sales and are not in Department 38 or making more than $12,000.00 a year.

The second query allows duplicate entries because it first selects employees from Department 38 and employees from outside Department 38 who work in Sales. Then, it adds employees whose salaries are more than $12,000.00.