How
to create relationship between three tables in Oracle
For example, the schema below uses two joins to relate three
tables. One join relates the Stu_Reg table
with the Select_Paper table
and the other join relates the Paper_Desc table
with the Select_Paper table.
Every Registration No in the Reg_No table is for a particular Student (and is uniquely
identified by a Reg_No). As shown in
the Select_Paper table, every Registration
No comprises one or more Papers for a particular Paper. Every Paper in the Paper_Desc table has a Paper name (and
is uniquely identified by a Paper_Code).
The following SQL statement would
achieve the required result:
SQL> Select
Stu_Name, Paper_Name from Stu_Reg, Select_Paper, Paper_Desc
Where
Stu.Reg.Reg_No=Select_Paper.Reg_No
and Paper_Desc.Paper_Code=Select_Paper.Paper_Code;
===============
Result (Output) ======================
|
STU_NAME
|
PAPER_NAME
|
|
Dippendu
|
RDBMS
|
|
Manas
|
RDBMS
|
|
Dippendu
|
VB.NET
|
|
Dip
|
C#.NET
|
|
Vijay
|
ASP.NET
|
|
Dip
|
ASP.NET
|
|
Raj
|
PHP
|
|
Manas
|
PHP
|
|
Raj
|
JAVA
|
SQL> Select
Stu_Name, Paper_Name from Stu_Reg, Select_Paper, Paper_Desc
Where
Stu.Reg.Reg_No=Select_Paper.Reg_No
and
Paper_Desc.Paper_Code=Select_Paper.Paper_Code ORDER BY STU_NAME;
===============
Result (Output) ======================
|
STU_NAME
|
PAPER_NAME
|
|
Dip
|
C#.NET
|
|
Dip
|
ASP.NET
|
|
Dippendu
|
RDBMS
|
|
Dippendu
|
VB.NET
|
|
Manas
|
RDBMS
|
|
Manas
|
PHP
|
|
Raj
|
JAVA
|
|
Raj
|
PHP
|
|
Vijay
|
ASP.NET
|
Try It........................
