JOIN three tables in PostgreSQL

Introduction

In this article we wil show you how to join three tables in PostgreSQL. In previous articles we have seen the introduction of the JOINs in PostgreSQL. The purpose of JOIN was to merge two tables column wise rather the merging the rows like the union operator. In many scenarios we need to merge the content of multiple tables in order to get the necessary insight. The JOIN helps a database user to see the complex information in an easier table form which makes it easy to analyze.

Demo of how to Join three tables in PostgreSQL

Let us consider a scenario of a market store where employees work on different counters. Each counter has one employee. There is multiple customers in the market, so in this scenario we have three different tables Employee, Payment, Customer. Let’s see the schema and queries required for this scenario, but first a reminder, in order for joins to work we had one simple rule for JOINs that there must be a foreign key relationship. This time we are going to JOIN three tables so we need to have a foreign key relationship between three tables. Let’s see how it’s done.

First, we will make Employee table with dummy records

CREATE TABLE Employees (
   employeeID INT PRIMARY KEY,
   name VARCHAR (255) NOT NULL
);

Insert records:

INSERT INTO Employees (
   employeeID,                
   name                      
) VALUES (1, 'Asmar'), (2, 'Ali'), (3, 'Hassan'), (4, 'Anna'), (5, 'Sau'), (6, 'Kelsie'), (7, 'Tory'),
(8, 'Salley');

The above queries will make a table of employees with the given dummy record

Image from Gyazo

The second table we will create is the Customer table:

CREATE TABLE Customer (
   customerID INT PRIMARY KEY,                  
   name VARCHAR (200)
);

Insert dummy record:

INSERT INTO Customer (
   customerID,                                  
   name            
)
VALUES (1, 'Ahmed'), (2, 'Moeez'), (3, 'Hussain'), (4, 'Abid'), (5, 'Sia'), (6, 'Kait'), (7, 'Tony'), (8, 'Sam');

This will create the customer table as below

Image from Gyazo

Now we will create the last table which is the Payment table. This table is different from other two as this will be the main table that will have all the foreign key relations from other two tables. Look carefully at how this table is created:

CREATE TABLE Payment (
   paymentID INT PRIMARY KEY,                  
   customerID INT,                
   employeeID INT, amount INT,
   FOREIGN KEY (employeeID)                          
   REFERENCES Employees (employeeID)                  
   ON DELETE CASCADE,
   FOREIGN KEY (customerID)
   REFERENCES Customer (customerID)
   ON DELETE CASCADE
);

This will create the schema with all necessary relations required for the joins to work.

Insert the dummy record:

INSERT INTO Payment (
   paymentID, customerID, employeeID, amount
)                                  
VALUES (1,2,1,2000), (2,1,2,3000), (3,4,3,3000), (4,5,4,4000), (5,3,5,4000), (6,6,6,5000),
        (7,7,7,5000), (8,8,8,5000);

This will create a table like this:

https://gyazo.com/9a2d325d2c6affac08567c4a53332877

Now we are setup to illustrate a three table join example, so let’s consider the situation where you need to how much each customer has paid to which employee. We need a query that can get this information quickly and effectively. Let us break the query in two parts. First we need to know which customer paid which employee. This can be achieved through a single join query on customer and payment table as:

SELECT cust.name, pay.amount
FROM Customer cust
INNER JOIN Payment pay ON pay.customerID = cust.customerID ;

This query will give us results about all customers and their respective payments. Now the second part of the main query is to know which employee received which payment. This can also be achieved through a simple join query on payment and employee tables as:

SELECT emp.name , pay.amount
FROM Employees emp
INNER JOIN Payment pay ON pay.employeeID = emp.employeeID ;

Now if you see both above queries you will notice that they both are same just referenced on to different tables. Now if we want to know which customer payed to which employee it quite simple, we just have to merge the above two queries with another join statement. This query will reference three tables and two join statements. Let’s see how to achieve this.

SELECT
              cust.name AS cust_name,
              emp.name AS emp_name,
              pay.amount
FROM
              Customer cust
              INNER JOIN Payment pay ON pay.customerID = cust.customerID
              INNER JOIN Employees emp ON pay.employeeID = emp.employeeID;

This query will give you a result set of how much customers paid to each employee. This query will first reference the customer and payment table with one join and then it will combine the resulting table with employee table as a second join. The result will look like this:

Image from Gyazo

Conclusion

In this article I have shown how anyone can JOIN three tables in PostgreSQL with the help of a scenario where this type of queries has a great importance because they are effective, easy to understand and small rather than having large subqueries.

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.