15.10 - Example: Creating a View that Defines a Self-Join with a Table - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

The following request creates a view that allows a personnel executive to keep track of employees who have more experience on the job than their supervisors. This request defines a self-join of the employee table. The correlation names workers and managers, created in the FROM clause, see the two temporary tables participating in the self-join.

    CREATE VIEW emp_info (workername,workeryrsexp,department, 
                          managername,manageryrsexp) AS
     SELECT workers.name, workers.yrsexp, workers.deptno, 
            managers.name, managers.yrsexp
     FROM employee AS workers, employee AS managers 
     WHERE workers.deptno = managers.deptno 
     AND managers.jobtitle IN ('Manager', 'Vice Pres') 
     AND workers.yrsexp > managers.yrsexp;