How to Make a Report From More Than One Table › How to Combine Data From More Than One Table

How to Combine Data From More Than One Table

Relational operations

When you combine data from two or more tables, you need to specify information that relates them somehow. Three relational operations, select, project, and join, can be used to define and access tables:

• Select enables you to choose which rows you want to include in your report.
• Project enables you to choose which columns you want to display.
• Join enables you to combine two or more tables on the basis of common values.

In the preceding chapter, you applied two of these relational operations:

• You selected which rows you want displayed in your report by specifying selection criteria.

select * from department where dept-id = 4000

┌────────────────────────────────────┐
│             DEPARTMENT             │
├─────────────────────┬──────────────┤        ┌────────────────────────────┐
│ DEPT-NAME           │ DEPT-ID      │        │       DEPT NAME/ID         │
├─────────────────────┼──────────────┤        ├──────────────────┬─────────┤
│ Computer Operations │ 3200         │        │ DEPT─NAME        │ DEPT─ID │
├─────────────────────┼──────────────┤        ├──────────────────┼─────────┤
│ Public Relations    │ 4000         ├───────►│ Public Relations │ 4000    │
├─────────────────────┼──────────────┤        └──────────────────┴─────────┘
│ Thermoregulation    │ 5200         │
└─────────────────────┴──────────────┘

Selecting rows:

• You projected which columns you want displayed in your report.

Project

select emp-last-name, dept-id from employee

┌────────────────────────────────────────────────────────────────┐
│                       EMPLOYEE Table                           │
├────────┬────────────────┬────────────────┬────────────┬────────┤
│ EMP-ID │ EMP-LAST-NAME  │ EMP-FIRST-NAME │ START-YEAR │ DEPT-ID│
├────────┼────────────────┼────────────────┼────────────┼────────┤
│  0235  │ Alanza         │ Mariana        │ 87         │  3200  │
│  1450  │ Enrique        │ Uriel          │ 76         │  4000  │
│  9811  │ Roberts        │ Ellen          │ 90         │  4000  │
│  6732  │ Ditka          │ Robert         │ 81         │  4000  │
│  7648  │ Carr           │ Eustace        │ 84         │  5200  │
│  5552  │ Chiu           │ Li             │ 86         │  5200  │
└────────┴──────┬─────────┴────────────────┴────────────┴───┬────┘
                │                                           │
                │                                           │
                └────────────────────┬──────────────────────┘
                                     │
                                     │
                                     │
                        ┌────────────▼────────────┐
                        │       EMP/DEPT-ID       │
                        ├───────────────┬─────────┤
                        │ EMP-LAST-NAME │ DEPT-ID │
                        ├───────────────┼─────────┤
                        │ Alanza        │ 3200    │
                        │ Enrique       │ 4000    │
                        │ Roberts       │ 4000    │
                        │ Ditka         │ 4000    │
                        │ Carr          │ 5200    │
                        │ Chiu          │ 5200    │
                        └───────────────┴─────────┘

Projecting Columns:

In this chapter you specify join criteria to combine data from the EMPLOYEE and DEPARTMENT tables into a report.

What are join criteria?

Join criteria are logical expressions that equate a column in one table with equivalent columns in additional tables. You must specify join criteria if you are reporting on more than one table at a time.

Joining tells CA OLQ which columns the tables have in common. By comparing the values in these columns, CA OLQ can match the rows in the tables and retrieve only those rows that the tables share.

Join

select depart.dept-name, department.dept-id, employee.emp-last-name
from employee, department where department.dept-id = employee.dept-id

┌─────────────────────────────────┐
│          DEPARTMENT             │
├───────────┬─────────────────────┤
│  DEPT-ID  │     DEPT-NAME       │
├───────────┼─────────────────────┤           ┌───────────────────────────────────────────────┐
│   3200    │ Computer Operations │           │             EMPLOYEES BY DEPARTMENT           │
│   4000    │ Public Relations    ├────┐      ├─────────────────────┬─────────┬───────────────┤
│   5200    │ Thermoregulation    │    │      │     DEPT-NAME       │ DEPT-ID │ EMP-LAST-NAME │
└───────────┴─────────────────────┘    │      ├─────────────────────┼─────────┼───────────────┤
                                       │      │ Computer Operations │ 3200    │ Alanza        │
       ┌──────────────────────────┐    │      │ Public Relations    │ 4000    │ Enrique       │
       │         EMPLOYEE         │    ├──────► Public Relations    │ 4000    │ Roberts       │
       ├────────────────┬─────────┤    │      │ Public Relations    │ 4000    │ Ditka         │
       │ EMP-LAST-NAME  │ DEPT-ID │    │      │ Thermoregulation    │ 5200    │ Carr          │
       ├────────────────┼─────────┤    │      │ Thermoregulation    │ 5200    │ Chiu          │
       │ Alanza         │ 3200    │    │      └─────────────────────┴─────────┴───────────────┘
       │ Enrique        │ 4000    │    │
       │ Roberts        │ 4000    ├────┘
       │ Ditka          │ 4000    │
       │ Carr           │ 5200    │
       │ Chiu           │ 5200    │
       └────────────────┴─────────┘

Joining the EMPLOYEE and DEPARTMENT tables:

How do you specify join criteria?

To join two tables, find a column that the two tables have in common. The columns do not have to have the same name, but they should:

• Have the same internal picture
• Have the same data type (for example, numeric)
• Contain some data that matches (in order to display data from both tables)

Where do you specify join criteria?

You specify join criteria in one of two places— on the Column Select screen, or on the Selection Criteria screen:

• On the Column Select screen, in the Selection criteria field next to the join column: For example, these join criteria join TABLE1 and TABLE2 based on common values of the ID and NUMBER fields:

     Columns Currently Selected:    0     Selection Criteria     Distinct N Y/N
    _ TABLE1
    X 03 ID
    X 03 NAME
    X 03 PHONE
    _ TABLE2
    _ 03 NUMBER                          eq ID
    X 03 SALARY
   Additional Selection Criteria:
  
  
                                     Proceed to Selection Criteria Screen? N Y/N
  

  (Note that you could also specify the join criteria, eq ID, next to the TABLE1 ID column.)

• On the Column Select screen, under Additional Selection Criteria:

     Columns Currently Selected:    0     Selection Criteria     Distinct N Y/N
    _ TABLE1
    X 03 ID
    X 03 NAME
    X 03 PHONE
    _ TABLE2
    _ 03 NUMBER                          eq id
    X 03 SALARY
   Additional Selection Criteria:
     name eq 'george'
  
                                     Proceed to Selection Criteria Screen? N Y/N
  

• On the Selection Criteria screen, under Additional Selection Criteria:

  146000 Type in selection criteria, and press the ENTER key.
   Please Enter Additional Selection Criteria:
  
    id eq number and (phone matches '617*******') and (salary gt
    30000 and salary lt 50000)
  

Example

You can join the EMPLOYEE and JOB tables because they both have columns that represent employee ID numbers (column ID in EMPLOYEE and column EMP-NUMBER in JOB). To join them, specify the following:

   Columns Currently Selected:    0     Selection Criteria     Distinct N Y/N
  _ EMPLOYEE
  X 03 ID
  X 03 NAME
  X 03 PHONE
  _ JOB
  _ 03 EMP-NUMBER                      eq ID
  X 03 SALARY
 Additional Selection Criteria:


                                   Proceed to Selection Criteria Screen? N Y/N

Note that you do not have to select the EMP-NUMBER column from JOB. If you did, the report would list the employee ID twice, once for ID and once for EMP-NUMBER.

What if columns have the same name?

If your common columns have the same name, you must include the table name in the join criteria. The table names and the column names are separated by a period (.).

For example, if both EMPLOYEE and JOB contain the ID field, specify:

 Columns Currently Selected:    0     Selection Criteria     Distinct N Y/N
_ EMPLOYEE
X 03 ID
X 03 NAME
X 03 PHONE
_ JOB
_ 03 ID                              eq employee.id
X 03 SALARY

What happens if you don't specify join criteria?

When the access mode is set to olq, CA OLQ automatically prevents you from leaving out your join criteria. If you select two tables and don't specify how to join them, CA OLQ issues a message reminding you to do so.