[Solved] joining 2 tables and save into third with duplicates [closed]

Question

A Student with just one Professor:

A third table is not necessary, just add that foreign key (idp = Professor ID) in the Student Table so that you can do:

SELECT * FROM Student 
    JOIN Professor ON Student.idp = Professor.idp 
    WHERE Professor.Name="Jon"

and this way you will get all the students who have Jon as their Professor.

A Student multiple professors

In case that a student have multiple professors. For sure you will need that third table then. Somethin like this:

Student_Professor {StudentID, ProfessorID} // Compound key

Another way to create that third table would be:

Student_Professor {StudentProfessorID, StudentID, ProfessorID}

where StudentProfessorID is your primary key and the rest foreign keys.. The advantage with this case is that you will only have to deal with one primary key instead of the compound one.

Query

Assuming that the third table is like this: Student_Professor {StudentID, ProfessorID}

We could use:

SELECT * FROM Student_Professor 
    JOIN Student ON Student.StudentID = Student_Professor.StudentID
    JOIN Professor ON Professor.ProfessorID = Student_Professor.ProfessorID
WHERE Professor.Name="Jon"

Accepted Answer