Search code examples
sqlinner-join

How can I use inner join to display two matching columns that correlate themselves in a third table by other two columns?

I have the following tables in SQL:

students:

primary key: student_id
name
email

courses:

primary key: course_id    
name

registration:

primary key: registration_id
start_date    
foreign key: student_id    
foreign key: course_id

What I want: Sql to display a table that correlates students names with course names, like this:

student name | course name    
john         | computer science    
alex         | architecture    
daisy        | engineering

I heard that I need to use Inner join to do this.

Solution

  • An INNER JOIN is indeed what is required:

    SELECT s.name, c.name
FROM students s
JOIN registration r ON r.student_id = s.student_id
JOIN courses c ON c.course_id = r.course_id

    Here's a small demo on dbfiddle...