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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+Sample table : customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
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
.............
.............
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics