SQL INSERT INTO from table 1 and 2 using Inner join-CodePudding

This is table Students:

StudentID       Firstname        Lastname
-------------------------------------------
1               John             Doe
2               Jane             Doenot

This is table Subjects:

SubjectID       Subject        Description
--------------------------------------------------------
1               EVEDRI         Event Driven Programming
2               DATSYS         Database Systems

I also created an empty table StudSubs with columns

StudentID (FK to Students) 
SubjectID (FK to Subjects)

My question is: I want to insert data from Students and Subjects tables into StudSubs, so that the StudSub table would look like this:

StudentID       Firstname       Lastname       Subject
-------------------------------------------------------
1               John            Doe            EVEDRI
1               John            Doe            DATSYS
2               Jane            Doenot         EVEDRI
2               Jane            Doenot         DATSYS

What is query code for my stored procedure to insert this data into StudSub?

CodePudding user response：

As I understand your question after your changes and your comment above, you don't need any JOIN at all.

You just want to select the data from both tables:

SELECT 
st.studentID,
st.FirstName,
st.LastName,
sj.Subject
FROM students st, subjects sj
ORDER BY st.studentID;

This will produce following result for your sample data:

StudentID       Firstname       Lastname       Subject
1               John            Doe            EVEDRI
1               John            Doe            DATSYS
2               Jane            Doenot         EVEDRI
2               Jane            Doenot         DATSYS

So your insert command would be this:

INSERT INTO StudSubs
SELECT 
st.studentID,
st.FirstName,
st.LastName,
sj.Subject
FROM students st, subjects sj;

Try out: db<>fiddle