JOINs on JOINs on JOINs
Tags: sql, codenewbie
Published: 2019-09-30 23:42:30

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](
https://media.giphy.com/media/V2L1CBBeGckPm/giphy.gif)
<figcaption>Via GIPHY</figcaption>

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](https://media3.giphy.com/media/l0IylOPCNkiqOgMyA/giphy.gif?cid=790b7611767ce13b381c4e278b3e9caf10786c0711665304&rid=giphy.gif)
<figcaption>Via GIPHY</figcaption>

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](https://raw.githubusercontent.com/kat-star/blog-images/master/sql_joins_chart.001.jpeg)

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](https://raw.githubusercontent.com/kat-star/blog-images/master/sql_joins_chart.002.jpeg)

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](https://raw.githubusercontent.com/kat-star/blog-images/master/carbon%20(1).png)

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

![chart3](https://raw.githubusercontent.com/kat-star/blog-images/master/sql_joins_chart.003.jpeg)

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

![code_snippet2](https://raw.githubusercontent.com/kat-star/blog-images/master/carbon%20(2).png)

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

![code_snippet3](https://raw.githubusercontent.com/kat-star/blog-images/master/carbon%20(3).png)

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](https://logicalread.com/join-more-than-two-sql-server-tables-mc03/#.XZFBKJNKhQI)
[A Visual Explanation of SQL Joins](https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/)