The next step is to determine which tables will be necessary for the query. That way, you won’t get lost in different column names and you’ll immediately know which column belongs to which table.Īt this point, our query should look like this: It’s important to use table names when listing your columns. Since we want to show students together with their courses, we’ll need three columns: student.first_name, student.last_name, and course.name. The first step is to look at the schema and select the columns we want to show. Joining 3 Tables Using a Junction Table Step 1 If you want to write your own queries that join multiple tables, you need to fully understand what’s happening in this query. The result of this query will look like this: first_name Is the code still confusing? Fear not – we’ll explain it thoroughly in the next section. This way, we can show the first and last name of each student together with the courses they’re attending. Then, we’re joining it with the student_course table and, finally, with the course table. Take a look at the code again:Īs you can see, we’re using the student table in the FROM clause. We’ve already seen the usage of the junction table. many rows in the table student_course can have the same course_id). many rows with the same student_id can be in the table student_course) and one course can be attended by many students (i.e. One student can be attending many courses (i.e. The relationship between the student and course tables is called a many-to-many relationship. Finally, looking at the course table, we can read that English literature and Python programming have IDs equal to 2 and 3, respectively. Then, looking at the student table, we can read that Shreya Bain has an ID equal to 1. From the second row, we see that the student with the ID equal to 1 is connected to the course with the ID equal to 3. The first row says that the student with the ID equal to 1 ( student_id column) is connected to a course with the ID equal to 2 ( course_id column). How do we know this? Take a look at the first two rows in the student_course table. The sole purpose of this table is to connect the student and course tables together.įor example, “Shreya Bain” (the student with id = 1) is connected to “English literature” (the course with id = 2) and “Python programming” (the course with id = 3). It’s important to notice that the student_course table is a junction table. Meanwhile, take a look at the example data from these three tables: We’ll use the teacher table later in the course in more advanced queries. teacher_id – The ID of the teacher for that course.course – Contains information about the courses:.student_course – Contains information about which students go to which courses:.last_name – The last name of the student.first_name – The first name of the student.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |