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.
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;
Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
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.
Did you find this tutorial useful? If so, We’d love for you to share it with some friends JUST CLICK HERE to Tweet the post.
Please do let us know what you thought in the comment section below – We’d love to hear your feedback.