The SQL statement in Figure 61 uses data from the Q.STAFF and Q.ORG tables to select all the clerks in the Eastern division.
If you check the sample tables in Appendix B, QMF Sample Tables, you see that the department numbers are found in both tables, the division name is in the Q.ORG table, and the job title is in the Q.STAFF table. In Q.ORG, the department number is in the DEPTNUMB column, and in Q.STAFF, the department number is in the DEPT column. You will join the tables by these two columns.
Specify all the columns you want to display on the report in the SELECT clause. Specify the tables you want to join in the FROM clause. Specify the columns whose values are equal in the WHERE clause, separated by an equal(=) sign.
Figure 61. This SQL query joins the Q.STAFF and Q.ORG tables.
SELECT DIVISION, ID, LOCATION, NAME
FROM Q.STAFF, Q.ORG
WHERE DIVISION = 'EASTERN'
AND JOB='CLERK'
AND DEPTNUMB = DEPT
ORDER BY ID
|
The report in Figure 62 displays when you run the query:
Figure 62. The report shows the data from both tables.
+--------------------------------------------------------------------------------+ | DIVISION ID LOCATION NAME | | ---------- ------ ------------- --------- | | EASTERN 80 WASHINGTON JAMES | | EASTERN 110 BOSTON NGAN | | EASTERN 120 ATLANTA NAUGHTON | | EASTERN 170 BOSTON KERMISCH | | EASTERN 180 ATLANTA ABRAHAMS | | EASTERN 190 WASHINGTON SNEIDER | +--------------------------------------------------------------------------------+
If you don't specify a common column when you join two tables, each row in the first table is joined to each row in the second table. The resulting report might contain duplicate data and might be very large.
The columns in the tables you are joining might have the same name. Use one of the following methods to distinguish between columns with the same name:
You can add a qualifier to identical column names to identify the table from which you selected the column.
For example, to distinguish between the PRODNUM column in the Q.PRODUCTS table, and the PRODNUM column in the Q.PROJECT table, add the following qualifiers to the column names:
The SQL statement in Figure 63 selects all the product numbers in both the Q.PRODUCTS and Q.PROJECT tables, the project numbers, departments, and product prices.
You need to specify only one of the duplicate column names when you select columns, because you combine the two columns in the report. Use a qualifier for duplicate column names everywhere you refer to them in the query.
Figure 63. This SQL query selects data from two columns with the same name.
SELECT PROJNO, Q.PRODUCTS.PRODNUM, DEPT, PRODPRICE FROM Q.PROJECT, Q.PRODUCTS WHERE Q.PRODUCTS.PRODNUM < 100 AND Q.PRODUCTS.PRODNUM = Q.PROJECT.PRODNUM |
Correlation names are names you use to identify the tables or views from which you selected columns when more than one column has the same name.
For example, to distinguish between the PRODNUM column in the Q.PRODUCTS table and the PRODNUM column in the Q.PROJECTS table, specify a correlation name of P for Q.PROJECT and a correlation name of S for Q.PRODUCTS.
Use the correlation name as a prefix to the column name wherever you refer to that column. The following query shows examples of using correlation names:
SELECT PROJNO, S.PRODNUM, DEPT, PRODPRICE FROM Q.PROJECT P, Q.PRODUCTS S WHERE S.PRODNUM < 100 AND S.PRODNUM = P.PRODNUM