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
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.
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.
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')
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.