SQL join three or more tables based on a parent-child relationship

has average rating 8 out of 10. Total 46 users rated.

<<PreviousNext>>

Description

In this page we are going to discuss about 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.

Example

Here is an example of SQL join three tables with condition.

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 :

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

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

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.