How to join more than two tables in in sql server

Here we will be joining 3 tables , the concept behind this is the relation between any tables joining should have a column related to it from another table.

SQL Script to create the required tables

Create Table Departments

(

     DepartmentID int primary key,

     DepartmentName nvarchar(50)

)

GO

Create Table Genders

(

     GenderID int primary key,

     Gender nvarchar(50)

)

GO

Create Table Employees

(

     EmployeeID int primary key,

     EmployeeName nvarchar(50),

     DepartmentID int foreign key references Departments(DepartmentID),

     GenderID int foreign key references Genders(GenderID)

)

GO

Insert into Departments values (1, 'IT')

Insert into Departments values (2, 'HR')

Insert into Departments values (3, 'Payroll')

GO

Insert into Genders values (1, 'Male')

Insert into Genders values (2, 'Female')

GO

Insert into Employees values (1, 'Mark', 1, 1)

Insert into Employees values (2, 'John', 1, 1)

Insert into Employees values (3, 'Mike', 2, 1)

Insert into Employees values (4, 'Mary', 2, 2)

Insert into Employees values (5, 'Stacy', 3, 2)

Insert into Employees values (6, 'Valarie', 3, 2)

GO

Write a query to join 3 the tables and retrieve EmployeeName, DepartmentName and Gender. 

SELECT DepartmentName, Gender, COUNT(*) as TotalEmployees

FROM Employees

JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID

JOIN Genders ON Employees.GenderID = Genders.GenderID

GROUP BY DepartmentName, Gender

ORDER BY DepartmentName, Gender