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:

-- Selecting specific columns: 'ord_num' from table 'a' (orders), 'cust_name' from table 'b' (customer),
-- 'cust_code' from table 'a' (orders), 'agent_code' from table 'c' (agents), and 'cust_city' from table 'b' (customer)
SELECT a.ord_num, b.cust_name, a.cust_code, c.agent_code, b.cust_city
-- Performing a Cartesian product (cross join) between tables 'agents' (aliased as 'c'), 'customer' (aliased as 'b'), and 'orders' (aliased as 'a') (implicit join)
FROM agents c, customer b, orders a
-- Defining join conditions in the WHERE clause where 'b.cust_city' equals 'c.working_area' to link 'customer' and 'agents', 
-- 'a.cust_code' equals 'b.cust_code' to link 'orders' and 'customer', and 'a.agent_code' equals 'c.agent_code' to link 'orders' and 'agents'
WHERE b.cust_city = c.working_area
AND a.cust_code = b.cust_code
AND a.agent_code = c.agent_code;

Explanation:

• This SQL query retrieves data from three tables: 'agents', 'customer', and 'orders'.


• It selects specific columns from these tables: 'ord_num' from 'orders', 'cust_name' and 'cust_city' from 'customer', 'cust_code' from 'orders', and 'agent_code' from 'agents'.


• The query performs a Cartesian product (cross join) between the 'agents', 'customer', and 'orders' tables, implicitly joining every row from each table with every row from the other tables.


• The join conditions are specified in the WHERE clause:


• 'b.cust_city' (customer city) must equal 'c.working_area' (agent working area), linking the 'customer' and 'agents' tables based on the city.


• 'a.cust_code' (customer code) must equal 'b.cust_code' (customer code), linking the 'orders' and 'customer' tables based on the customer code.


• 'a.agent_code' (agent code) must equal 'c.agent_code' (agent code), linking the 'orders' and 'agents' tables based on the agent code.


• This query retrieves data about orders, including the order number, customer name, customer city, customer code, and agent code.


• By joining these tables based on the specified conditions, the query retrieves information about orders along with the corresponding customer and agent details.


• Cartesian product may result in a large intermediate result set, and hence, it's essential to ensure that the join conditions are correctly specified to avoid unnecessary rows in the output.

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
	.............
	.............

See our Model Database

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