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.