Self-Joins and Aliases

 < Day Day Up > 



Self-joins are a way of joining a table to itself. You may need to use one as a way of restricting rows in a table or because the same values are used in two columns in the same table. A classic example is that of employees. Employees have managers. Thus, the employee ID in one column is also used in the manager ID in another column.

The following example uses data from Oracle's HR sample schema. In this schema, every employee has a supervisor, except for the main boss, Steven King (thus, you need an outer join for this one employee). Managers may supervise one or more employees. As you see in Figure 7-7, the Employee Id is also used to indicate the Manager Id in the same table. So Employee ID 100, Steven King, manages Neena Kochhar (101) and Lex De Haan (102).

click to expand
Figure 7-7: Steven King does not report to a manager. Neena Kochhar, Lex De Haan, and others are managed by Steven King.

To implement the self-join, first create an alias for the EMPLOYEE called EMP_MANAGER, as shown next. If you wanted to know the supervisor's job title and salary at the same time you wanted to know each reporting employee's job title and salary, you would also create an alias for JOB called JOB_MANAGER.

click to expand

Next, create the join that uses the alias. By using the alias, you are self-joining the EMPLOYEE_ID field in one table to the MANAGER_ID field in the same table; the alias makes it appear as if you are joining two different tables. To get a list of any employees that do not report to a manager, include an outer join on the EMP_MANAGER alias.

click to expand

This last part is tricky. If you left the universe the way it is now, it would work fine as long as objects from EMP_MANAGER were never used in a report by itself. For example, if you tried to create a report that listed managers only, all employees would be listed on this report, as the self-join would not be activated: David Austin (105) would appear on the report, even though he is never listed in the EMLOYEE.MANAGER_ID column, as shown in Figure 7-6. To ensure that you get a list of managers only, you must force the self-join.

Forcing a Join

There are two ways to ensure the self-join is activated any time you select objects from the EMP_MANAGER alias. The first, most obvious way is to include the join as a WHERE clause in each object definition. The second, less obvious way is to force the join by selecting EMPLOYEE as a Table in the object definition. This second way is preferable, as the join statement is added to the SQL statement once; with the WHERE clause in each object definition, the WHERE statement is added to the SQL statement multiple times according to how many objects contain the condition.

To force a join, modify the object definition. Click Tables and use CTRL-click to select the two tables EMP_MANAGER and EMPLOYEE.

Tip 

Forcing a join to a table is also useful for security purposes, as discussed in Chapter 12.



 < Day Day Up > 



Business Objects(c) The Complete Reference
Cisco Field Manual: Catalyst Switch Configuration
ISBN: 72262656
EAN: 2147483647
Year: 2005
Pages: 206

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net