Joining tables to themselves
1. Joining tables to themselves
Let's take another look at the themes table.2. The themes table
Notice that besides the theme id and the name, there's a column parent id. That means the table has a relationship to itself. This is a hierarchical table, and having a parent id is a common way to represent this hierarchical relationship. For example, the themes "Arctic Technic", "Competition", "Expert Builder", and "Model" all have the parent id 1. Based on the first column, we can see that that ID corresponds to the first theme, called "Technic". We can also see that the next five rows have the parent id 5, which we can see corresponds to the theme "Model".3. The hierarchy of themes
These ten observations communicate a hierarchy of themes like this. The Technic theme has four children, and its child theme Model also has five children themes. How could we explore the relationship between themes and parents? Well, you've already learned to join two tables together. But did you know you can also join a table to itself, by matching each theme to its parents?4. Child-parent table
If we inner join themes to itself, and use the parent id column on the left to match the id column on the right, we end up with a table of child-parent relationships. Notice that Arctic Technic, under name-dot-x, is now linked to its parent, Technic. Similarly, Airport is linked to its parent Model.5. Adding a suffix
We can make this a bit more readable by noticing that the dot x is the child and dot y is the parent, and adding a suffix argument. We now have a table of themes alongside the name of their parent themes, which is convenient for exploring the hierarchy of themes.6. Lord of the Rings themes: parent
For example, there are LEGO sets that are themed around the fantasy series "The Lord of the Rings". You could filter on name-underscore-child to find out what the theme's parent is. This filter shows that the parent theme is called "The Hobbit and Lord of the Rings."7. Lord of the Rings themes: children
What if you wanted to find out all of the children of that theme instead? You could filter on the name-underscore-parent attribute instead. This shows that the three children of that theme are the three books in the Lord of the Rings trilogy: The Fellowship of the Ring, the Two Towers, and the Return of the King.8. The Lord of the Rings trilogy
Based on all this, we start to understand the shape of our data. In the exercises you'll explore these hierarchical relationships some more, and how you could use the left join verb from this chapter to discover wh themes have no children.9. Let's practice!
Let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.