How to combine these two tables to create a new table?-CodePudding

Hi I have two tables here:

Transcript

Grade   Student_number  Course_number   Semester     Year   
A       8               MATH2410        Fall         07 
A       8               CS1310          Fall         07 
B       8               CS3320          Spring       08 
B       17              MATH2410        Fall         08 
C       17              CS1310          Fall         08 
A       8               CS3380          Fall         08

Student

Name   Student_number   Class   Major
Smith  17               1       CS
Brown  8                2       CS

As you can see, they have a common Student_number. I want a table that looks like this:

Grade   Student_number  Course_number   Semester     Year   Name
A       8               MATH2410        Fall         07     Brown
A       8               CS1310          Fall         07     Brown
B       8               CS3320          Spring       08     Brown
B       17              MATH2410        Fall         08     Smith
C       17              CS1310          Fall         08     Smith
A       8               CS3380          Fall         08     Brown

How do I do this?

I tried using insert and left join but neither worked. How do I achieve the third table? Thanks!

CodePudding user response：

18 people have seen it, but none could figure out this question, so I did it myself with a bit of research.

SELECT STUDENT.Name, TRANSCRIPT.Grade, TRANSCRIPT.Student_number, TRANSCRIPT.Course_number, TRANSCRIPT.Semester, TRANSCRIPT.Year 
FROM TRANSCRIPT 
LEFT JOIN STUDENT 
ON TRANSCRIPT.Student_number = STUDENT.Student_number

CodePudding user response：

If your first two tables are still in use, maybe view is a better choice, and if Transcript.Student_number is must from Student, just use INNER JOIN.

create or replace view view_students_transcripts as SELECT t.*,s.name FROM Transcript t inner join Student s ON s.Student_number = t.Student_number;