SQL SELECT using multiple tables

Question

I'm pretty new to using MySQL and I'm still learning.

Say I have 3 Tables in a Database School. The Tables are as follows;

Course Contains ID(PK) and Name

Student Contains ID(PK), Name, etc

Enrolment Contains rol_num(PK), student_id and course_id.
student_id references student(id) course_id references course(id)

Basically Students details in the Students table, Courses details in the Courses Table, Enrolment table is a link table of students and their courses

Using a Select query command how can I get the following fields for all students courses.

student.id, student.name, course.name

Showing all Courses each student in on along with their name and id.

p.s. I'm British who speaks the one true form of English. It's Enrolment. :P — Stealthbird97
We just kept the spelling the same as the original spelling from the languages English words come from. Americans changed it to be easier to spell as it's more like how it sounds. Anyway, This conversations detracting from the usefulness of the original post. — Stealthbird97

Abhik Chakraborty Abhik Chakraborty · Accepted Answer · 2014-06-13T20:54:59

You need to JOIN the tables as

select
s.id,
s.name as student_name,
c.name as course_name
from Enrolment e
join student s on s.id = e.Student_id
join  Course c on c.id = e.course_id

SQL SELECT using multiple tables

1 Answers