How to Use Join and String_agg in Microsoft SQL Server

Photo by MI PHAM on Unsplash

In this article, we’ll look at how to use join on more than two tables and aggregate the result using the function STRING_AGG() in Microsoft SQL Server.

If you don’t know about Microsoft SQL Server, I’ll briefly explain to you what it is.

😃

What is Microsoft SQL Server?

Microsoft SQL Server is a Relational Database Management System that revolutionized how businesses handle data. It helps to store and manage data.

If you’re familiar with other relational database management systems such as MySQL, or PostgreSQL then picking up Microsoft SQL Server is easy.

I’m running on the default instance of SQL server.

Now, let’s consider a problem.

Problem

Let’s say we have three tables namely Employee, Projectand EmployeeProject. The below image is the relational database design:

Figure 1: Relational Database Design for the problem

The problem is to get all the employee details and their corresponding projects.

Figure 2: Tables from left to right: Employee, EmployeeProject, and Project

Things to consider: not all the employees from table Employeemapped with table EmployeeProject and not all the projects in table Project mapped with table EmployeeProject .

Our main goal is to retrieve all the employee details from table Employee whether they are mapped with EmployeeProject or not.

We can try to solve this problem by using joins. As you can see, we have to join three tables in order to solve this problem. First, we need to join tables Employeeand EmployeeProject . Then with the resulted table, we’ll join Project .

Let’s go through some scenarios to solve this problem.

Scenario 1

Let’s go with INNER JOIN everywhere!

SELECT e.Id, e.FirstName, e.LastName, e.Designation, e.City, ep.ProjectId 
FROM Employee…