Back to cookbooks list Articles Cookbook

How to Join on Multiple Columns

  • JOIN
  • INNER JOIN
  • ON

Problem:

You want to join tables on multiple columns by using a primary compound key in one table and a foreign compound key in another.

Example:

Our database has three tables named student, enrollment, and payment. The student table has data in the following columns: id (primary key), first_name, and last_name.

idfirst_namelast_name
1EllieWillson
2TomBrown
3SandraMiller

The enrollment table has data in the following columns: primary key (student_id and course_code), is_active, and start_date.

student_idcourse_codeis_activestart_date
1GD03true2020-01-20
1AP01false2020-03-10
2SL01true2020-05-05
3SL01true2020-06-01

The payment table has data in the following columns: foreign key (student_id and course_code, the primary keys of the enrollment table), status, and amount.

student_idcourse_codestatusamount
1GD03paid230
1AP01pending100
2SL01pending80
3SL01pending110

Let’s show each student’s name, course code, and payment status and amount.

Solution:

SELECT
  s.last_name, s.first_name,
  p.course_code, p.status, p.amount
FROM enrollment e
JOIN student s
  ON s.id=e.student_id
JOIN payment p
  ON p.course_code=e.course_code
	AND p.student_id=e.student_id;
last_namefirst_namecourse_codestatusamount
WillsonEllieGD03paid230
WillsonEllieAP01pending100
BrownTomSL01pending80
MillerSandraSL01pending110

Discussion:

If you’d like to get data stored in tables joined by a compound key that’s a primary key in one table and a foreign key in another table, simply use a join condition on multiple columns.

In one joined table (in our example, enrollment), we have a primary key built from two columns (student_id and course_code). In the second table (payment), we have columns that are a foreign compound key (student_id and course_code). How can we join the tables with these compound keys?

Easy! We just need to use a JOIN clause with more than one condition by using the AND operator after the first condition. In our example, we use this condition:

p.course_code=e.course_code AND p.student_id=e.student_id

In the first part, we use the student_id column from the enrollment table and student_id from the payment table. In the next condition, we get the course_code column from the enrollment table and course_code from the payment table.

Note that the student_id and course_code columns form a primary key in the enrollment table. Therefore, they’re used in the payment table as a foreign key.

Recommended courses:

Recommended articles:

See also: