Confused with mysql statement, three tables

Question

So i have this problem of printing out table data from 3 different tables as a matrix. And after searching internet, i fanally found a solution to my problem. But i have no idea, how does that mysql statement work. This is a example that i want to do

and i think i found a solution so if some one could help explaining me of how it works?

SELECT names.codename,
s1.score AS "Score1", s1.comment AS "Comments1",
s2.score AS "Score2", s2.comment AS "Comments2",
SUM(st.score) AS "Total"
FROM students names 
LEFT JOIN scores s1 ON s1.act_id=1 AND names.id=s1.student_id 
LEFT JOIN scores s2 ON s2.act_id=2 AND names.id=s2.student_id 
LEFT JOIN scores st ON names.id=st.student_id
WHERE names.codename <> ''
GROUP BY names.codename
ORDER BY names.codename;

students table:
+----+---------------+
| id | codename      |
+----+---------------+
|  1 | Budy          |
+----+---------------+

assignments table:
+--------+------------+
| act_id | name       |
+--------+------------+
|      1 | Activity 1 |
|      2 | Activity 2 |
+--------+------------+

scores table:
+------------+--------+-------+
| student_id | act_id | score |
+------------+--------+-------+
|          1 |      1 |    10 |
|          1 |      2 |    10 |
+------------+--------+-------+

Now the problem was, I wanted to have the assignments listed across the top, and the scores next to the names. Something like this:

+---------------+------------+------------+-------+
| codename      | Activity 1 | Activity 2 | Total |
+---------------+------------+------------+-------+
|          budy |         10 |         10 |    20 |
+---------------+------------+------------+-------+

Answer 1

Try this

SELECT s.codename, SUM(IF(act.act_id=1, act.score, 0)) AS 'Activity 1', SUM(IF(act.act_id=2, act.score, 0)) AS 'Activity 2', total.score AS 'total' 
FROM
    students s
    INNER JOIN (SELECT student_id, act_id, SUM(score) AS score FROM scores GROUP BY 1, 2) act ON s.id=act.student_id
    INNER JOIN (SELECT student_id, SUM(score) AS score FROM scores GROUP BY 1) total ON s.id=total.student_id
GROUP BY 1

This will return results for only the students having records in the scores table. If you want to list all students, you'll have to change INNER JOIN to LEFT OUTER JOIN

UPDATE

I've updated the query and removed the first sub-query

SELECT student_id, act_id, SUM(score) AS score FROM scores GROUP BY 1, 2

this is not needed if student_id and act_id are a composite key so we can use the scores table directly. This way the query becomes

SELECT s.codename, SUM(IF(act.act_id=1, act.score, 0)) AS 'Activity 1', SUM(IF(act.act_id=2, act.score, 0)) AS 'Activity 2', total.score AS 'total' 
FROM
    students s
    INNER JOIN scores as 'act' ON s.id=act.student_id
    INNER JOIN (SELECT student_id, SUM(score) AS score FROM scores GROUP BY 1) total ON s.id=total.student_id
GROUP BY 1

Explanation

The JOIN between student and scores fetches all scores for all students (I added another student with id 2 and name Pal), e.g.

+----+----------+------------+--------+-------+
| id | codename | student_id | act_id | score |
+----+----------+------------+--------+-------+
|  1 | Buddy    |          1 |      1 |    10 |
|  1 | Buddy    |          1 |      2 |    10 |
|  2 | Pal      |          2 |      1 |    15 |
|  2 | Pal      |          2 |      2 |    10 |
+----+----------+------------+--------+-------+

The sub-query

SELECT student_id, SUM(score) AS score FROM scores GROUP BY 1

fetches sum of scores for each student like this

+------------+-------+
| student_id | score |
+------------+-------+
|          1 |    20 |
|          2 |    25 |
+------------+-------+

And we put the results together in the main SELECT part using

SELECT s.codename, SUM(IF(act.act_id=1, act.score, 0)) AS 'Activity 1', SUM(IF(act.act_id=2, act.score, 0)) AS 'Activity 2', total.score AS 'total'...

we add scores if act_id=1 in column 'Activity 1', similarly we add scores in column 'Activity 2' if act_id=2 and lastly we use the sum from sub-query as 'total'

I hope this clarifies your questions