JOIN three tables in PostgreSQL
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
employeeID INT PRIMARY KEY,
name VARCHAR (255) NOT NULL
) VALUES (1, 'Asmar'), (2, 'Ali'), (3, 'Hassan'), (4, 'Anna'), (5, 'Sau'), (6, 'Kelsie'), (7, 'Tory'),
The above queries will make a table of employees with the given dummy record
The second table we will create is the Customer table:
customerID INT PRIMARY KEY,
name VARCHAR (200)
Insert dummy record:
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
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:
paymentID INT PRIMARY KEY,
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:
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),
This will create a table like this:
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:
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:
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.
cust.name AS cust_name,
emp.name AS emp_name,
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:
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started