Generated Many-to-Many Chains
Chains are derived relationships, from two or more existing built-in or custom joins you group together and expose through one single connector. We automatically create and show instead in models chain relationships across any intersection table, for many-to-many relationships.
For instance, there is no direct built-in link between Orders and Products here below, they pass through an intersection table as you may see here:
However, you may expand instead the generated chain between them and bypass the intersection table. We automatically take care of all joins in the SQL statement.
Drill-Down on Chains
On your drill-down, you may still access the hidden table rows, which is great when you need more information:
Create Big Chain in Query
Chains may simplify your life when you traverse multiple irrelevant tables, because they do not contain the information you are looking for. Suppose you want to see how many suppliers each of your customer used and your query looks like this:
In a query or model, to define a new custom chain, select all connectors you want to group together, right click on one of them and select Create Chain Relationship. A new chain connector will appear and, in a query, all intermediate tables will disappear. You can use the chain joins just like the regular joins. The slightly modified query below selects ALL customers, even those with no suppliers at all:
Create Chain in Model
The diagram below on the left shows the expanded built-in relationships between three tables. There is no direct relationship between Artist and Track. But if you frequently look for all tracks per artist, skipping (or rather bypassing) the Album table, it might may sense to create a chain relationship. Select both relationship connectors (with CTRL+mouse click), right click on one of them and select the Create Chain Relationship contextual command. A new connector will appear between Artist and Track. On mouse over, it will highlight all shapes and connectors from its path: