Блог

Извините, я задал еще один вопрос, потому что мой последний вопрос слишком запутанный.

РЕДАКТИРОВАТЬ: Мой текущий ЗАПРОС

 UPDATE student_subject 
  JOIN subject_bsit
    ON subject_bsit.subject_id = student_subject.sub_id
   SET enrolled = 1 
 where student_subject.student_id = 1235 
   and student_subject.sub_id = 1
  

Чего я хочу, так это.

Student_id= 1235 пытается зарегистрировать subject_id=5, поэтому это утверждение не должно выполняться, потому что subject_id= 5 имеет предварительное условие для subject_id=1 или программирования 1

Но если, например, в student_subject sub_id 1 и зарегистрировано = 1, что означает, что студент закончил программирование и уже зарегистрирован сейчас, студент = 1235 теперь может зарегистрировать subjectid = 5

Я понятия не имею, как выполнить этот запрос. Большое вам спасибо.

 THIS IS THE TABLE

    "student"

    -----------------------
    |studentID | FullName |
    -----------------------
    |1234      | John    |
    |1235      | Michael |
    |1236      | Bryce   |

"subject_bsit"

    -----------------------------------------
    |subject_id| subject_name  |  pre_id    |
    -----------------------------------------
    |    1     | Programming 1 |    0     |
    |    2     | Networking    |    0     |
    |    3     | Algorithm     |    0     |
    |    4     | Physical Educ |    0     |
    |    5     | Programming 2 |     1       |

This is the Junction table to connect the 
    two now.

"student_subject"

    ------------------------------------------------
    | student_id | subject_id | Grade   | Enrolled |
    ------------------------------------------------
    |   1235     |      1     |    0    |     0    |
    |   1235     |      2     |    0    |     0    |
    |   1235     |      3     |    0    |     0    |
    |   1234     |      1     |    0    |     0    |
  

Комментарии в строке.

 Private conString As String = "Your connection string"

Private Sub EnrollStudent(StudentID As Integer, SubjectID As Integer)
    'Question Does Subject have a prerequisite?
    Dim Prerequisite As Integer
    Using cn As New MySqlConnection(conString)
        Using cmd As New MySqlCommand("Select pre_id from subject_bsit 
                                        where subject_id = @subject_id", cn)
            cmd.Parameters.Add("@subject_id", MySqlDbType.Int32).Value = SubjectID
            cn.Open()
            Prerequisite = CInt(cmd.ExecuteScalar)
        End Using
    End Using
    'Answer - No, It is OK to enroll student there are no prerequisites
    If Prerequisite = 0 Then
        InsertEnrollment(StudentID, SubjectID)
        Return
    End If
    'Answer - Yes there Is a prerequisite
    'New Question - Has the student passed the prerequisite?
    Dim PassingGrade As Integer
    Using cn As New MySqlConnection(conString)
        Using cmd As New MySqlCommand("Select Grade From student_subject 
                                        Where subject_id = @subject_id 
                                        And student_id = @student_id", cn)
            cmd.Parameters.Add("@subject_id", MySqlDbType.Int32).Value = Prerequisite
            cmd.Parameters.Add("@student_id", MySqlDbType.Int32).Value = StudentID
            cn.Open()
            PassingGrade = CInt(cmd.ExecuteScalar)
        End Using
    End Using
    'Answer - Yes, student has a passing grade.
    If PassingGrade > 0 Then
        InsertEnrollment(StudentID, StudentID)
    Else 'Answer - No, student does not have a passing grade in prerequisite.
        MessageBox.Show("Student cannot enroll because of prerequisite.")
    End If
End Sub

Private Sub InsertEnrollment(StudentID As Integer, SubjectID As Integer)
    Dim query = "Insert Into student_subject (student_id, subjectId, Grade, Enrolled) 
                Values (@student_id, @subject_id, 0, 1);"
    Using cn As New MySqlConnection(conString)
        Using cmd As New MySqlCommand(query, cn)
            cmd.Parameters.Add("@student_id", MySqlDbType.Int32).Value = StudentID
            cmd.Parameters.Add("@subject_id", MySqlDbType.Int32).Value = SubjectID
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using
    MessageBox.Show("Successful enrollment")
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    EnrollStudent(1235, 5)
End Sub
  

Редактировать

Здесь опечатка…

 If PassingGrade > 0 Then
    InsertEnrollment(StudentID, StudentID)
  

Это должно быть

 If PassingGrade > 0 Then
    InsertEnrollment(StudentID, SubjectID)
  

Вторым аргументом является SubjectID