how to join more than 2 tables in MS SQL

How to Join more than 2 Tables in MS SQL

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 *

    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

    Similar Posts

    Leave a Reply

    Your email address will not be published. Required fields are marked *