In this page we are going to discuss about such a join, which is made up by using two tables which contain a composite PRIMARY KEY (i.e. a PRIMARY KEY made up of more than one columns of table) and FOREIGN KEY.
Here is an example of composite key in SQL between two tables.
Sample table : student
Sample table : studentreport
To get 'name', 'title', 'class', 'section' and 'rollid' from table 'student' and 'grade' and 'semister' from table 'studentreport' together after a joining, with following conditions -
1. 'class', 'section' and 'rollid' combination is primary key in 'student' table
2. 'class', 'section' and 'rollid' combination is foreign key in 'studentreport' table which is referencing to the primary key of 'student' table.
3. 'class', 'section' and 'rollid' of 'student' and 'studentreport' must be same.
the following sql statement can be used :
SELECT a.name,a.title,a.class,a.section, a.rollid,b.grade,b.semester FROM student a, studentreport b WHERE a.class=b.class AND a.section=b.section AND a.rollid=b.rollid;
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.