Many to Many relationship get all grouped records

I have the following three tables in a mysql database:

grades:

subjects:

grades_subject:

The grades table contains an id (primary key) student_id and quiz scores. A student_id can appear multiple times in this table.

The Subjects table is a simple list of subjects with a primary key.

The grades_subject table links the two with a subject id, grade id (The st_id links another table with student information). All three are a primary keys.

As you can see, a subject id can appear many times in this table as each student in the grades table can have multiple subjects. 20001 appears twice, once with subject id 1 and again with subject id 2 etc. Each subject id can also appear numerous times as numerous student_ids in the grades table can share one subject.

Im tyring to get all records in the grades table, grouped by subject id. So the result would be:

sbj_id 1...then all the students in that class with their respective grades. sbj_id 2... and so one for all subjects.

I have tried various solutions from this site and many combinations of different types of joins, groupings and also used select distinct. I either get duplicate values, or the correct records in the grades table but only 2 subject_ids, or student grades getting repeated for each subject and other inaccurate results.I have also tried subqueries.

This is the closest I have gotten to the correct results (I understand I do not neet the group by grades_subject.sbj_id in the below query, its just to demonstrate that it works):

SELECT grades.*, grades_subject.sbj_id
FROM grades
JOIN grades_subject ON grades.st_id = grades_subject.grd_id
where grades_subject.sbj_id = 49
group by grades_subject.sbj_id, grades_subject.grd_id

It results in all the scores for all students in that class which demonstrates that with my current setup I should be able to get what I am looking for with the right query or grouping. When I remove the where clause, I should get each sbj_id, and then all students in that subject with their scores, then the next subject etc. However, it will retrieve the scores for the first student in the subject, then duplicate those results for all other students in that subject as shown below:

Select Distint does not work, various join types do not work, selecting and grouping by ids in the grades table does not work including the primary id. Instead of listing everything I have tried that does not work...

How I can get my desired results with my current set up without changing my table setup as other tables and coding are built on it. The set up works well with all other types of queries that I need except when Im trying to get all student grades accross all subjects. If I must change the set up, please ellaborate on the simplest way.

Answers

To achieve the desired result of retrieving all records in the grades table grouped by subject_id, you need to properly join the grades table with the grades_subject table based on the st_id and grd_id columns. Then, you can group the results by subject_id. Here's how you can do it:

SELECT g.*, gs.sbj_id
FROM grades g
JOIN grades_subject gs ON g.st_id = gs.grd_id
ORDER BY gs.sbj_id, g.st_id;

This query will retrieve all records from the grades table, joined with the grades_subject table based on the student IDs. It will then order the results first by subject_id and then by student_id.

If you want to see the result grouped by subject_id, you can use a GROUP BY clause:

SELECT gs.sbj_id, g.*
FROM grades g
JOIN grades_subject gs ON g.st_id = gs.grd_id
GROUP BY gs.sbj_id, g.st_id
ORDER BY gs.sbj_id, g.st_id;

This query will group the results by subject_id, showing all records for each subject grouped together. However, please note that using GROUP BY without aggregation functions on non-aggregated columns may not always give you the desired results. In MySQL, it will return arbitrary rows for each group. So, it's important to consider the implications of using GROUP BY in your specific scenario.

If you want to retrieve all records in the grades table, including the subject IDs, without changing the table structure, the above queries should work for you.