Are you working on SQL queries and your brain is awhirl trying to figure out how to join multiple tables? Do you see the SQL keywords and table/column names as just a big blob of words? I can attest that when I learned SQL and started making my own queries trying to gather data across three or more tables, Tasmanian Devil spinning like a vortex accurately represented what was going through my mind.
As I am a visual learner, I decided to take pen to paper to sketch out the schema of my database and it was slowly starting to make sense, albeit a bit conspiracy theory looking.
Having my schema sketched out with the column names/attributes below each table helped me visualize where my joins needed to take place. The first step is to draw out all of the tables with the column names under.
Then, draw out the connections between your tables, indicating the type of relationship between your tables. In my example, a player has many games_players (the join table) and a games_players belongs to a player. Each line will represent one join in your SQL query.
Now we can construct our SQL query. It’s helpful to keep in mind what our end result should be. In this case, I want a table with the names of the players and the number of games they played in. Since the player’s names are in the players table, I will start from the players table. Then we can make our first join to the games_players table on the id columns.
As we still need to connect to the games table, we know there is one more join to be made.
Now we can get games in on the party as well, and also have the join happen on the ids columns.
Finally, we will want to GROUP BY players.names because we want the resulting dataset to be a list of the players.
And that’s it! We should get two snazzy columns with our players and the count of their games. If there’s one big takeaway from my post, it’s that for every x tables you need to join, you will need (x-1) join clauses. Also, do what you have to do to understand the material, even if your whiteboard or notebook starts to look like scribbles and nonsense.
Joining More Than Two SQL Server Tables
A Visual Explanation of SQL Joins
Top comments (0)