CODEWITHSUNDEEP
mysqlsqlselectmany-to-manyrelational-database
Stumbler
Stumbler

Reputation: 2146

many-to-many select: missing data

In a university db I have a many-to-many relationship between students and modules that they are registered to: using a bridging table of status.

The bridging table status has a multi-field primary key (using the unique combination of student and module; stud_id and mod_id)

However, when returning data from a general select query designed to list all this data:

SELECT status.stud_id, student.fname, student.sname, status.mod_id, modle.mtitle, status.grades
FROM status
INNER JOIN modle
ON status.mod_id=modle.mod_id 
INNER JOIN student
ON status.stud_id=student.stud_id 
GROUP BY status.stud_id

the query will not take into account that the students may have many modules, and will only display students as having a single module.

Upvotes: 0

Views: 82

Answers (1)

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

Why do you use GROUP BY status.stud_id? That's the reason why you have only one result for student.

I don't see any aggregate function within your query, so you probably can just deleteGROUP BY:

SELECT status.stud_id, student.fname, student.sname, status.mod_id, modle.mtitle, status.grades
FROM status
INNER JOIN modle
ON status.mod_id=modle.mod_id 
INNER JOIN student
ON status.stud_id=student.stud_id

Upvotes: 1

Related Questions