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.
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
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
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework