How can I insert values from two tables into the third one?

See more:

C#

WPF

MySQL

MVVM

Hello everyone,

I'm currently struggling with inserting some values from the register form into the database.I have 2 tables in which I want to insert data:RegisterTeacher and TeacherCourses.Now,I have a problem with the logic here,although I have searched over the internet to see different possibilities of doing this.These are the tables:

CREATE TABLE [dbo].[RegisterTeacher] (
    [SNTeacher] INT            NOT NULL,
    [UserName]  NVARCHAR (50)  NOT NULL,
    [pwd]       INT            NOT NULL,
    [fullName]  NVARCHAR (MAX) NOT NULL,
    [education] NVARCHAR (50)  NULL,
    PRIMARY KEY CLUSTERED ([SNTeacher] ASC)
);

CREATE TABLE [dbo].[AvailableCourses] (
    [courseName] NVARCHAR (MAX) NOT NULL,
    [education]  NVARCHAR (50)  NOT NULL,
    [ClassID]    NVARCHAR (50)  NULL,
    [courseID]   INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([courseID] ASC)
);

CREATE TABLE [dbo].[TeacherCourses] (
    [courseID]  INT        NOT NULL,
    [SNTeacher] INT        NOT NULL,
    [IDKey]     NCHAR (10) NOT NULL,
    PRIMARY KEY CLUSTERED ([IDKey] ASC),
    CONSTRAINT [FK_TeacherCourses_ToTable] FOREIGN KEY ([SNTeacher]) REFERENCES [dbo].[RegisterTeacher] ([SNTeacher]),
    CONSTRAINT [FK_TeacherCourses_ToTable_1] FOREIGN KEY ([courseID]) REFERENCES [dbo].[AvailableCourses] ([courseID])
);

Now what i what to achieve is the following:For the RegisterTeacher table it works to save the values,as I am doing it like this:

connectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\User\\Desktop\\BackupVersion\\LastAndFinalVersion\\DatabaseStudents.mdf;Integrated Security=True";
          using (SqlConnection con = new SqlConnection(connectionString))
          {
              sql = "Insert into RegisterTeacher(SNTeacher,UserName,pwd,fullName,education)values(@SNTeacher,@UserName,@pwd,@fullName,@education)";

              con.Open();
              using (SqlCommand cmd = new SqlCommand(sql, con))
              {
                  cmd.Parameters.AddWithValue("@SNTeacher", SqlDbType.Int).Value=SNTeacher;
                  cmd.Parameters.AddWithValue("@UserName", SqlDbType.NVarChar).Value = UserName;
                  cmd.Parameters.AddWithValue("@pwd", SqlDbType.Int).Value = pwd ;
                  cmd.Parameters.AddWithValue("@fullName", SqlDbType.NVarChar).Value = fullName ;
                  cmd.Parameters.AddWithValue("@education", SqlDbType.NVarChar).Value=education;
                  cmd.ExecuteNonQuery();
              }

My problem is when I want to save the courseName into the table TeacherCourses.I would like to both save the SNTeacher and courseName-where courseName is taken from AvailableCourses and passed with the courseID reference to the TeacherCourses table along with SNTeacher from RegisterTeacher table.How would the syntax look like?

What I have tried:

What I have tried is the following:I have made a separate query to insert data into TeacherCourses:

using (SqlCommand com = new SqlCommand(query, con))
                      {
                  query = "Insert into TeacherCourses (courseID,SNTeacher) select courseID,SNTeacher from RegisterTeacher p inner join AvailableCourses c ON p.courseID=c.courseID  AND p.SNTeacher=c.SNTeacher";
                  SelectedCourses = new ObservableCollection<AvailableCours>();
                  foreach (var item in Courses)
                  {

                      if (item.IsChecked)
                      {
                          SelectedCourses.Add(item);

                      }
                      com.Parameters.AddWithValue("@courseName", SqlDbType.NVarChar).Value = item.courseName;

                      com.ExecuteNonQuery();