Sql Junction Table in java

Question

I want to use many to many relationship. how can do student with subjects and subject with students in sql.

the student table have

student_IDno | full name | address |

and the subject table have

subject_IDno| title | description | time |

i want to assign the student id number to subject table so that the subject table can have students

can i do the same thing in subject table? assign the subject id number to the student table so it can have a subjects too ?

and if i search the subject code number students will appeared, vice versa with student if i search students code subjects will appeared also.

what would be the best way to do it ? i hope i did good of explaining my problem thanks guys.

Answer 1

You introduce a THIRD table to establish the relationship between tables STUDENT and SUBJECT. Lets call the table CLASS. It will have the following columns:

classID | student_IDno | subject_IDno

Lets say a student enrolled in the school. An entry will be INSERTed to STUDENT with a unique student_IDno. (Lets say 1)

The list of Subjects are of course located on the SUBJECT table.

Now if the student wants to enroll on a subject, he will have to join a CLASS. Now you INSERT him into a class with student_IDno = 1 and subject_IDno = whatever subjects he enroll in.

Your CLASS table will then look like:

classID | student_IDno | subject_IDno
   1    |      1       |     1
   2    |      1       |     2
   3    |      1       |     3

can you see how it shows how many CLASSes student 1 has?

How do you do a SEARCH? Lets say you want to find the students who are taking SUBJECT 1.

SELECT student.* FROM CLASS as class
JOIN SUBJECT as subject ON class.subject_IDno = subject.subject_IDno
JOIN STUDENT as student ON class.student_IDno = student.student_IDno 
WHERE subject.subject_IDno = 1

I leave the rest to you. I think you get the idea.

more on SQL join here