#Redshift create table as select code

In this case, let’s go with the student table. Personally, I like starting with a table that isn’t a junction table. In theory, it can be any of the tables we’re using. The first task is to choose the table which will go in the FROM clause. In the final part, we’ll have to join all the tables together. Looking at the database schema, we see that student_course is a junction table between these two. However, we’ll have to find a way to join these tables. There are two obvious ones: student and course. 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

#Redshift create table as select code

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.student – Contains information about students:.Our example data is stored in four tables.Right now, we’ll focus on the first three tables: For further information about reading a schema, see the article Crow’s Foot Notation on the Vertabelo blog. For example, there’s a reference between the student and student_course tables – each student can be linked to multiple rows in the student_course table. In the above entity-relationship diagram (ERD), you can see tables, their columns, the columns’ data types, and the references between tables. We’ll get deeper into the query and tables in the next section. We’ve simply repeated the JOIN clause and joined three tables. ON student.id = student_course.student_id The truth is that you can easily extend this idea to three tables or even more. That’s not surprising – this concept can be hard to understand, and the idea that JOINs can get even more complicated may be really scary at first. If you’ve just learnt JOINs in SQL, you might think that it’s limited to two tables. It covers a wide range of topics from simple 2-table JOINs, through joining multiple tables and using OUTER JOINs, to joining a table with itself. It contains over 90 hands-on exercises that let you refresh your SQL JOINs knowledge. The best way to practice SQL JOINs is 's interactive SQL JOINs course. You can join 3, 4, or even more! The possibilities are limitless. Using JOIN in SQL doesn’t mean you can only join two tables.