Back
When an additional table is added to a report, it is joined with the previous table – in other words, the two tables are combined into one larger table based on the rows of the original. Joining tables is the key to creating more complex reports, as it takes advantage of the relationships that the tables share.
Keys
Not all tables can be joined together. In order to be joined, the two tables in question must share common data. For instance, the Locations table can be joined with the Companies table because they share a company_id column. This column, which serves as the link between the tables, is referred to as a key.
Join Order
When two tables are joined, the system attempts to match the rows in each table based on their keys. Any matches found will be combined into one row. Any rows in the second table that have no match in the original table will be discarded.
For this reason, the order in which tables are added to a report is significant: the first table serves as the base to which the second table adds. Rows in the first table that have no match will be kept, but rows in the second table that have no match will not.
For instance, if there are 500 contacts in the system, and 100 of them hold memberships, then adding the Contacts table followed by the Membership table will yield a report with 500 results. If the Membership table is added first, only 100 results will be returned – the 400 contacts who did not have membership will not be included in the results.
Multiple Matches
If the second table contains multiple matches on a key in the first table, a row will be created for each match. For instance, if the Contacts and Membership tables are both included, and a contact has had 3 historical memberships, that contact will appear three times in the final report, once for each membership.