0

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.

3
Contributors
4
Replies
5
Views
5 Years
Discussion Span
Last Post by soUPERMan
1

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.

Votes + Comments
Good advice on the departments. Even if they are not few, best practice will be to have a new table
0

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
0

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.

0

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    |                |
+-----------+---------+------+-----+---------+----------------+
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.