SQL join three or more tables based on a parent-child relationship
In this page, we are going to discuss such a join which involves the participation of three tables and there is a parent-child relationship between these tables.
A parent-child relationship between two tables can be created only when there is a PRIMARY KEY in one table and FOREIGN KEY in another table.
Syntax diagram - SQL JOIN of three tables

Example: SQL JOIN - three or more tables
Here is an example of SQL join three tables with conditions.
Sample table: agents
Sample table: customer
Sample table: orders
To get 'ord_num' and 'cust_code' columns from the table 'orders' and 'cust_name' and 'cust_city' columns from the table 'customer' and 'agent_code' column from the table 'agents' after a joining, with following conditions -
1. 'a', 'b' and 'c' are the aliases of 'orders', 'customer' and 'agents' table,
2. 'orders' and 'customer' tables are child table of 'agents' table because 'agent_code' is primary key in 'agents' table and foreign key in 'customer' and 'orders' table,
3. 'orders' table is child table of 'customer' table because 'cust_code' is primary key in 'customer' table and foreign key in 'orders' table,
4. 'cust_city' and 'working_area' of 'customer' and 'agents' table must be same,
5. 'cust_code' of 'orders' and 'customer' table must be same,
6. 'agent_code' of 'orders' and 'agents' table must be same,
the following SQL statement can be used :
SQL Code:
SELECT a.ord_num,b.cust_name,a.cust_code,
c.agent_code,b.cust_city
FROM agents c,customer b,orders a
WHERE b.cust_city=c.working_area
AND a.cust_code=b.cust_code
AND a.agent_code=c.agent_code;
Output:
ORD_NUM CUST_NAME CUST_CODE AGENT_CODE CUST_CITY ---------- ---------------------------------------- ---------- ---------- ----------- 200114 Bolt C00002 A008 New York 200122 Martin C00003 A004 Torento 200118 Karl C00023 A006 London 200119 Ramanathan C00007 A010 Chennai 200121 Karolina C00008 A004 Torento 200130 Ravindran C00025 A011 Bangalore 200134 Winston C00004 A005 Brisban 200108 Karolina C00008 A004 Torento 200103 Jacks C00021 A005 Brisban 200105 Ravindran C00025 A011 Bangalore 200109 Sundariya C00011 A010 Chennai 200101 Micheal C00001 A008 New York 200111 Albert C00020 A008 New York 200104 Shilton C00006 A004 Torento 200106 Sasikant C00005 A002 Mumbai 200125 Fleming C00018 A005 Brisban 200117 Rangarappa C00014 A001 Bangalore 200123 Avinash C00022 A002 Mumbai 200120 Ramesh C00009 A002 Mumbai 200116 Charles C00010 A009 Hampshair 200124 Srinivas C00017 A007 Bangalore 200126 Avinash C00022 A002 Mumbai ............. .............
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Join two tables related by a composite primary key or foriegn key pair
Next: Using a where clause to join tables based on nonkey columns
SQL: Tips of the Day
Selecting unique values from a column:
Use the DISTINCT operator in MySQL:
SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;
Ref: https://bit.ly/3wFScsF
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook