Watch the step-by-step guide on Joining More Than 2 Tables In MS SQL: The Easy Way
Join more than 2 Tables in MS SQL -Requirements:
- MS SSMS or any other IDE for SQL purposes
- Data tables
To download SSMS please click here: Microsoft website
To download the please fill up the form.
Upload and View the Data / csv file in SSMS using MS SQL
- Uploading the Data in SSMS is very simple and straightforward. Users need to right-click on the Database, select the “tasks” option, then click on import flat file.
- Fill in the information such as location, click next, preview Data, click next, modify the data, click next and at last finish and upload the Table
- If the data does not appear in your Database, you must click on the “Refresh” icon on the top.
Selecting the Data / Tables in SSMS using MS SQL
By using the MS SQL code you can select multiple columns at once. For each table, you must write the block of query again.
Select * from table name
Select * from table name2
This code will select 2 tables and show all the columns in both tables separately.
As shown in the video, our code will be
select * from department select * from employee select * transaction
Note: In MS SQL “select *” actual meaning in English is “Select all”
Inner Join 2 Tables / Data / CSV in MS SQL
To join 2 tables we have multiple choices of joining in SQL
- Left Join
- Right Join
- Inner Join
- Outer Join
- Cross Join
- Full outer Join
To do SQL inner join to join 2 tables you must write this query :
select column_names from Table1 inner join table2 on id.table1 = id.table2
While following the video, and to join the data in the video you must write this query to join the employee table with the department table
Select e EmployeeNumber, e EmployeeFirstName, e Department, sum(t.amount) as Pending_amount from employee e inner join transaction t on e.employeeNumber = t.EmployeeNumber group by e EmployeeNumber, e.EmployeeFirstName, d.department
Here, group by is used as a dimension to show the aggregated amount of Measure. The group y is only used when a Measure/ aggregation is used such as Sum(column), Avg(column), and others. The beginner method is to copy all the Dimensions from the Select query. The Dimensions are opposite of the Measures such as Name, City, Department, ID, and country, and the list keeps on going. So, copy all these Dimensions and put them in Group By Query, this will allow SQL to understand that the calculated measure should be calculated upon grouped by dimensions.
Join more than 2 Tables in MS SQL
select e.EmployeeNumber, e.EmployeeFirstName, e.Department, d.Department_Head, sum(t.Amount) from [dbo].[employee] e inner join [dbo].[transaction] t on e.EmployeeNumber = t.EmployeeNumber inner join [dbo].[department] d on e.Department = d.Department group by e.EmployeeNumber, e.EmployeeFirstName, e.Department, d.Department_Head