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:
SELECT student_number, title, forename, surname, course name
FROM student, course
WHERE student.course_number = course.course_number;
- 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).