A Dynamic JOIN Build is an easy way to create a join between tables/views without having to type the SQL statement as it is done in a Dynamic SQL view.
When clicking “Dynamic JOIN Builder” in the Databases menu the following form will appear:
After selecting a Connection it is possible to select different tables/views from the Database connection.
These tables/views can then be joined by selecting fields to match. Its possible to select between Lookup (left join) and Enforce (inner join) when defining fields to join on.
After defining the JOIN Build its possible to see a sample of the returned dataset (Click “Run”) and the generated SQL statement (Click SQL).
When viewing the returned dataset a count of the number of records returned is shown in the top of preview window.
This can be helpful to verify that the joins has been set up correctly.
Often a Dynamic JOIN Build will be more effective than a normal written Dynamic SQL View. When a report is based on a Dynamic JOIN Build but only includes fields from i.e. 2 out of 4 joined tables; Dynamic AI will automatically change the view to not include irrelevant Lookups (Left Joins).
In the above example the view would i.e. be changed from:
SELECT *
FROM (((Transactions DAI_
INNER JOIN Companies J2 ON
DAI_.Company = J2.CompanyNo)
LEFT JOIN Contacts J3 ON
DAI_.Contact = J3.ContactID)
LEFT JOIN Products J4 ON
DAI_.Product = J4.ProductID)
To i.e.:
SELECT
J2.CompanyName AS F101400001DAI0, J4.ProductGroupName AS F101400002DAI1,
SUM(DAI_.Amount) AS F101400003DAI2
FROM ((Transactions DAI_
INNER JOIN Companies J2 ON
DAI_.Company = J2.CompanyNo)
LEFT JOIN Products J4 ON
DAI_.Product = J4.ProductID)
GROUP BY J4.ProductGroupName, J2.CompanyName
ORDER BY J4.ProductGroupName, J2.CompanyName
If the report doesn’t include data from the Contacts table. Note that the “LEFT JOIN Contacts” has been excluded from the SQL statement. The result of above would in a report look like:
