Solving m2m Joins via Bridge tables

Solving m2m Joins via Bridge tables

Creating Bridge Tables in order to link tables via one-to-many relationships

When to use

Use when you need to build a table of unique values in order to link tables with duplicate values in the join column via one-to-many joins

Instructions

An example of this type of challenge is where we have Sales and Budget tables which each contain a Category column with multiple instances of each category.  To create a bridge table:

• Edit your queries
• Create staging queries (for each table)
  • Right-click the query (Sales or Budget) > Reference
  • Right-click the desired column > Remove other columns
  • Right-click the column > Remove duplicates
  • Disable the Load
  • Optional: Rename the query as “Staging-<original table name><new table name>  (Staging-SalesCategories)
• Create a new “dimension” query
  • Right-click one of the Staging queries > Append Queries
  • Append the other staging query
  • Right-click the column > Remove duplicates
  • Rename the table to <new table name> (Categories)
• Click Close & Apply to load the data
• Verify tables are linked correctly
  • Hide the fields on the “many” side

Hints & tips

• Using this approach ensures that the table being created will always hold every unique item from both tables being linked.