# Start coding here...#exercise Joining parts and part categories
The inner_join is the key to bring tables together. To use it, you need to provide the two tables that must be joined and the columns on which they should be joined.
In this exercise, you'll join a list of LEGO parts, available as parts, with these parts' corresponding categories, available as part_categories. For example, the part Sticker Sheet 1 for Set 1650-1 is from the Stickers part category. You can join these tables to see all parts' categories!
Instructions 1/2 50 XP 1 2 Add the correct joining verb, the name of the second table, and the joining column for the second table.
Now, use the suffix argument to add "_part" and "_category" suffixes to replace the name.x and name.y fields.
#script
Add the correct verb, table, and joining column
parts %>% inner_join(part_categories, by = c("part_cat_id" = "id"))
Use the suffix argument to replace .x and .y suffixes
parts %>% inner_join(part_categories, by = c("part_cat_id" = "id"), suffix = c("_part", "_category"))
#exercise Joining parts and inventories
The LEGO data has many tables that can be joined together. Often times, some of the things you care about may be a few tables away (we'll get to that later in the course). For now, we know that parts is a list of all LEGO parts, and a new table, inventory_parts, has some additional information about those parts, such as the color_id of each part you would find in a specific LEGO kit.
Let's join these two tables together to observe how joining parts with inventory_parts increases the size of your table because of the one-to-many relationship that exists between these two tables.
Instructions 100 XP Connect the parts and inventory_parts tables by their part numbers using an inner join.
#script
Combine the parts and inventory_parts tables
parts %>% inner_join(inventory_parts, by = "part_num")
#exercise Joining in either direction
An inner_join works the same way with either table in either position. The table that is specified first is arbitrary, since you will end up with the same information in the resulting table either way.
Let's prove this by joining the same two tables from the last exercise in the opposite order!
Instructions 100 XP Connect the inventory_parts and parts tables by their part numbers using an inner join.
#script
Combine the parts and inventory_parts tables
inventory_parts %>% inner_join(parts, by = "part_num")
#exercise Joining three tables
You can string together multiple joins with inner_join and the pipe (%>%), both with which you are already very familiar!
We'll now connect sets, a table that tells us about each LEGO kit, with inventories, a table that tells us the specific version of a given set, and finally to inventory_parts, a table which tells us how many of each part is available in each LEGO kit.
So if you were building a Batman LEGO set, sets would tell you the name of the set, inventories would give you IDs for each of the versions of the set, and inventory_parts would tell you how many of each part would be in each version.
Instructions 100 XP Combine the inventories table with the sets table. Next, join the inventory_parts table to the table you created in the previous join by the inventory IDs.
#script sets %>%
Add inventories using an inner join
inner_join(inventories, by = "set_num") %>%
Add inventory_parts using an inner join
inner_join(inventory_parts, by = c("id" = "inventory_id"))
#exercise What's the most common color?
Now let's join an additional table, colors, which will tell us the color of each part in each set, so that we can answer the question, "what is the most common color of a LEGO piece?"
Instructions 1/2 50 XP 1 2 Inner join the colors table using the color_id column from the previous join and the id column from colors; use the suffixes "_set" and "_color".
Count the name_color column and sort the results so the most prominent colors appear first.
#script
Add an inner join for the colors table
sets %>% inner_join(inventories, by = "set_num") %>% inner_join(inventory_parts, by = c("id" = "inventory_id")) %>% inner_join(colors, by = c("color_id" = "id"), suffix = c("_set", "_color"))