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
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Check out our new Ask the Experts feature: Our SQL gurus are waiting to answer your toughest questions.