Problem solve Get help with specific problems with your technologies, process and projects.

Computing values from different child segments

New SQL users can join computed records from two different branches of an entity's data schema with this script.

One of the most difficult problems that new SQL users encounter is the attempt to join computed records from two different branches of an entity's data schema. Databases often contain data that resides in treelike structures. At some point in the database, the branch splits. For example, a department can have multiple employees and an employee can have tool purchases and eyeglass purchases. In this example the schema branches for the tool purchases and eyeglass purchases. These records are contained in tables related to the Employee table.

When I ask a student to compute values from each of the branches in the same SELECT statement the student invariably gets the answer wrong. The students have learned how to join tables and use group functions. However, they do not understand that you cannot join the child detail records from two schema branches to their parents. Listing 1 (below) records (emp_tools), and this virtual record to the Eyeglass Purchase records (glasses). This is what causes the error. There is absolutely no relationship between the child detail records for Tools and Glasses except they have the same parent.

Listing 1: SELECT statement joining two child detail branches

SQL> select last_name, first_name, sum(tool_cost), sum(cost)
  2  from employee, emp_tools, glasses
  3  where employee.payroll_number = emp_tools.fk_payroll_number
  4  and employee.payroll_number = glasses.fk_payroll_number
  5  group by last_name, first_name;

LAST_NAME       FIRST_NAME      SUM(TOOL_COST) SUM(COST)
--------------- --------------- -------------- ---------
COOLIDGE        CALVIN                      35       350
EISENHOWER      DWIGHT                     375        45
FORD            GERALD                      12       435
JOHNSON         ANDREW                    16.7       330
NIXON           RICHARD                   18.5       246
REAGAN          RONALD                    28.7       495
ROOSEVELT       FRANKLIN                    20       258
TAFT            WILLIAM                     23       145
WILSON          WOODROW                 116.95       369

9 rows selected.

SQL>

When you want to compute child values from different branches, you must join the computed virtual records rather than the detail child records. This is accomplished by using a view or in-line view to compute one of the values. Listing 2 (below) illustrates this using an in-line view to calculate the total cost of Eyeglass purchases. Notice the differences from the results in Listing 1.

Listing 2: SELECT statement joining two computed records

SQL> select last_name, first_name, sum(tool_cost), eyeglass_cost
  2  from employee, emp_tools,
  3   (select fk_payroll_number, sum(cost) eyeglass_cost
  4    from glasses
  5    group by fk_payroll_number) e
  6  where employee.payroll_number = emp_tools.fk_payroll_number
  7    and employee.payroll_number = e.fk_payroll_number
  8  group by last_name, first_name, eyeglass_cost;

LAST_NAME       FIRST_NAME      SUM(TOOL_COST) EYEGLASS_COST
--------------- --------------- -------------- -------------
COOLIDGE        CALVIN                      35           175
EISENHOWER      DWIGHT                     375            15
FORD            GERALD                      12           145
JOHNSON         ANDREW                    16.7           165
NIXON           RICHARD                   18.5           123
REAGAN          RONALD                    28.7           165
ROOSEVELT       FRANKLIN                    20           129
TAFT            WILLIAM                     23           145
WILSON          WOODROW                 116.95           123

9 rows selected.

SQL>

Reviewing the Table Relationship drawing for the database schema will tell you if the technique is needed. If the data comes from two different branches, a view/in-line view will be needed. If all the needed data resides on one continuous set of branches (i.e. department, employee, tools or department, employee, glasses) you do not need the technique.

About the Author

John Palinski has been an Oracle developer and project manager for the last fourteen years. He developed one of one of the first relational database work management systems in the country. Mr. Palinski also teaches his own Oracle courses at Iowa Western Community College and the University of Nebraska at Omaha. Mr. Palinski is the author of the "Oracle Database Construction Kit" published by QUE and provides custom Oracle training worldwide through his consulting business, Realistic Software Training.

For More Information


Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close