Hierarchies

Self/Reflexive Joins

Self or reflexive joins are just a particular form of joins, on the same table, in which we create a pair of expandable relationship items. They can be renamed to more properly illustrate the relationship.

For instance, the query below uses twice the same table of Employees. However, one shape represents the Managers, the other the Subordinates, and they have been renamed accordingly. There may be managers with no subordinate (and yes, there are, look at the rows with empty cells on the right), so we click on connector close to Managers shape to get ALL managers. We also clicked on Hide Nulls and Hide Consecutive Duplicates for a better report-like cleaner view:

Expandable Views

From Objects browser or a model shape, select a reflexive foreign key item or column, a reflexive relationship item or column, or a reflexive connector. Call Create Expandable View from the contextual menu. This will create one special query view per such relationship. You may get an error if the total number of table rows is larger then the Max Expandable Records (by default 10,000). This is because this simple query brings all table rows and links the records on the client side, so it can be slow and consume a lot of resources.

Otherwise, it displays all rows with a first expandable column as below. You may expand or collapse all rows with toolbar buttons. The key columns of this manager-subordinates reflexive relationship – MGR (the foreign key) and EMPNO (the primary key) – are displayed first. Two special columns are added at the end: Level (the depth level, starting from 1) and Path (collection of all parents and current row primary key values):

We also detect, break and highlight cycles. Based on the MGR-EMPNO pair values, FORD-SMITH-JONES forms a loop: the manager of FORD is JONES, which is also a subordinate. We broke the chain on FORD and displayed it in red.