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:

chain2

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:

chain1

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:

chain3

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:

chain4

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:

chain-relationships