Skip to main content

Linking Tables Example

Let us put this into practice.

We will produce a report, which shows a list of all students, together with the name of the course that they are studying. If you examine the student table you will see that it contains the course number (course_number) but not the course name. This is to be found in the course table (course_name).

Therefore, we need to link the student and course tables together. We do this using the common column (course_number) as follows:

The join statement linking two tables

SELECT student_number, title, forename, surname, course name

FROM student, course

WHERE student.course_number = course.course_number;

Note:

  • You do not need to include the joining column in the list of columns being selected
  • If you do include the joining column in the list of columns being selected then it must be prefixed with one of the table names (see example over) containing that column
  • Each column participating in the join is prefixed with the name of the table
  • If two tables are being joined then you need at least one join statement. If three tables are being joined then you need at least two join statements, etc. (see example above).

Next: Extending Links