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.

Just add one more table -- `studentsubject` for example with the `studentid` and `subjectid` as the composite primary key. Then you can query either way and join accordingly. — sgeddes, Nov 02 '15 at 00:09
how would the student_id and subject_id will have number ? where will i get their id numbers ? — unknown, Nov 02 '15 at 00:12
The student_id and subject_id values in the new table will come from the students table (student_IDno) and subjects table (subject_IDno) that you want to "link". — Mark Sholund, Nov 02 '15 at 00:16
This might be of interest: https://megocode3.wordpress.com/2008/01/04/understanding-a-sql-junction-table/ — Mark Sholund, Nov 02 '15 at 00:17
Ok i'm getting the idea now . when it will happen? after i added student_id to the subject and subject_id to student? am i right ? that will be the time that i added the student_id and subject_id values to the junction table student_subject to its composite primary key student_id and subject_id am i right ? — unknown, Nov 02 '15 at 00:27
Some people find posts easier to read when they use proper capitalization, such as I rather than i. So, if you'd like your question to be read by more people, consider editing it to correct such flaws. — , Nov 02 '15 at 04:50

score 1 · Accepted Answer · answered Nov 02 '15 at 04:58

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.

Sql Junction Table in java

1 Answers1