Some short instructions on how to accomplish a join on three or more SQL database tables but also using multiple keys/columns as well.
Good StackOverflow reference:
https://stackoverflow.com/questions/24639504/mysql-join-3-tables-using-multiple-columns-keys
Three different tables used:
1. UserDivisions
An old legacy table containing Division and it’s ID:
2. MediaCategoryDivisionMap
This table maps the Division ID of the first table to the Category ID of a newer MediaCategories table:
3. MediaCategories
Finally the newer table that uses a Category ID:
I wish to use the Division IDs in table UserDivisions in order to extract the equivalent Category IDs they are mapped to in MediaCategoryDivisionMap and therefore obtain the new Name contained in the MediaCategories table.
As the StackOverflow post states
“INNER JOIN creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate.”
We use the multiple INNER JOIN to first get the first result from tables UserDivisions and MediaCategoryDivisionMap and do a further INNER JOIN to combine this result with that
obtain from MediaCategories:
SELECT u.ID, u.Division, map.CategoryId, cat.Name
FROM [Snowball_45_DEV].[dbo].[UserDivisions] as u
INNER JOIN [Snowball_45_DEV].[dbo].[MediaCategoryDivisionMap] AS map ON map.DivisionId = u.ID
INNER JOIN [Snowball_45_DEV].[dbo].[MediaCategories] AS cat ON cat.Id = map.CategoryId
WHERE u.IsProductLine = 1
Giving the following query result, where Division is the old name, and Name is the modernised version, obtained by doing the inner joins on the tables. From doing this query I can now list the old and new names side by side:
