MYSQL many to many confusion

[soUPERMan]

I have two tables namely student and subject joined by a table called student_subject.
With the following structure
student:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| student_ID     | int(11)      | NO   | PRI | NULL    | auto_increment |
| studentCode    | varchar(8)   | NO   |     | NULL    |                |
| studentName    | varchar(255) | NO   |     | NULL    |                |
| studentType    | varchar(50)  | NO   |     | NULL    |                |
| visaIssueDate  | date         | YES  |     | NULL    |                |
| visaExpiryDate | date         | YES  |     | NULL    |                |
| parentID       | int(11)      | YES  | MUL | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

subject:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| code       | varchar(255) | NO   |     | NULL    |                |
| name       | varchar(255) | NO   |     | NULL    |                |
| department | varchar(255) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

student_subject:

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| studentID | int(11) | NO   | MUL | NULL    |                |
| subjectID | int(11) | NO   | MUL | NULL    |                |
| grade     | char(2) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

I want to query all the subjects for a particular student using the field student.student_ID. How do i go about doing that?
Thanks.

[diza]

Normally if you have the student_id, use the many to many table joined to the subject table. If you do not have the student_id but the student name, join the student table too so you can use the student name to get all connected subjects. Also if there are only a few departments, I would advice to make a separate table for that one too.

[seslie]

Hi soUPERMan,

A 'join' clause would be best suitable for what you are trying to do. See this thread for help on join. Try your hands in it and post back any issues you might have.

also try this:

select su.name
from subject su,
     student st,
     student_subject ss
where ss.studentID = st.studentid
     and ss.subjectID = su.subjectID

[soUPERMan]

Thanks for the response.
I used the following query after messing around a bit:

SELECT subject.code, subject.name, subject.department, student_subject.grade, student_subject.mark, student_subject.hours FROM student,subject,student_subject 
    WHERE student.student_ID = student_subject.studentID AND subject.id = student_subject.subjectID AND studentID =[a_specific_student_id];

Any comments or advice is greatly appreciated.

[soUPERMan]

PS: I've added a few colums to the student_subject table, the query statement above reflects those fields.
Here's the new structure for student_subject:

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| studentID | int(11) | NO   | MUL | NULL    |                |
| subjectID | int(11) | NO   | MUL | NULL    |                |
| grade     | char(2) | YES  |     | NULL    |                |
| mark      | int(11) | YES  |     | NULL    |                |
| hours     | int(11) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+