- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Creating Bridge Tables in order to link tables via one to many relationships
Exercise files
Download this lesson’s related exercise files.
Solving m2m Joins via Bridge tables49.5 KB Solving m2m Joins via Bridge tables - Completed
47.6 KB
Quick reference
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.
Sorry, we don’t have a transcript for this lesson yet.
Lesson notes are only available for subscribers.