Perform
the following steps to join a table to itself and use the resulting
hierarchy of tables in a query:
-
Create
an SQL query in an empty job. The query should contain the SQL Join
transformation, at least one source table, and a target table.
-
Open the
Designer window for the SQL Join transformation. Click
Create in the Navigate pane to access the
Diagram tab and the SQL Clauses pane.
-
Drop the
same table that was used as a source table for the query in the
Diagram tab. You are prompted to supply an alias for
the table because it is already being used as a source table for the
query. Enter the alias in the
Alias field
of the properties pane for the table. The dialog box for the alias
is shown in the following display.
Self-Join Alias Dialog Box
-
Complete
any additional configuration needed to finish the query. The following
display shows a sample job that includes a table joined to itself.
Sample Job with a Table Joined to Itself
The tables
in the flow shown on the
Diagram tab are
reflected in the FROM clause that is highlighted on the
Code tab below it. The query that is shown in the sample
job pulls the Name variable from the original table (denoted with
the
us
alias). However, it pulls the
Population and Area variables from the copy of the original table
(denoted with the
uscopy
alias).