DEV Community

Katherine Kelly
Katherine Kelly

Posted on

JOINs on JOINs on JOINs

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.

taz spinning

Via GIPHY

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.

charlie-it's always sunny conspiracy

Via GIPHY

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.

chart

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.

chart2

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.

code_snippet1

As we still need to connect to the games table, we know there is one more join to be made.

chart3

Now we can get games in on the party as well, and also have the join happen on the ids columns.

code_snippet2

Finally, we will want to GROUP BY players.names because we want the resulting dataset to be a list of the players.

code_snippet3

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

Discussion (0)