w3resource logo


SQL join two tables related by a composite columns primary key or foreign key

Secondary Nav


In this page, we are going to discuss 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 a 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 the 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,
FROM student a, studentreport b
WHERE a.class=b.class 
AND a.section=b.section
AND a.rollid=b.rollid;


NAME                           TITLE                     CLASS SECTION      ROLLID GRADE SEMISTER
------------------------------ ------------------------- ----- -------- ---------- ----- ----------
Robert                         Paul                      VI    A                 2 A+    2Nd
Danny                          Moris                     V     B                15 AA    1St
Robert                         Paul                      VI    A                 2 A+    1St
Deepak                         Saxana                    V     A                15 AA    2Nd
Deepak                         Saxana                    V     A                15 A++   1St

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.

Join our Question Answer community to learn and share your programming knowledge.

Help the community:

Python: Fizzbuzz

C++: Decimal to binary conversion

JavaScript: Need Help in JavaScript

Python: Help me with this program