How to Join on Multiple Columns

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.

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

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.

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;

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.