Styjun

Sorry i made another question because my last question is too confusing.

EDIT: My Current QUERY

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

What i want to happen is.

Student_id=1235 is trying to enroll subject_id=5 so that statement must not happen because subject_id=5 has a Prerequisite to subject_id=1 or programming 1

But if example in the student_subject sub_id 1 and Enrolled=1 meaning the student finish the programming and already enrolled now the student=1235 can now enroll the subjectid=5

I don't have any idea how to this query . Thank you so much.

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 |

Comments in line.

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

EDIT

Typo here...

If PassingGrade > 0 Then
 InsertEnrollment(StudentID, StudentID)

This should be

If PassingGrade > 0 Then
 InsertEnrollment(StudentID, SubjectID)

The second argument is SubjectID