关于内联接和左、右连接的用法。（鸟语版）

 

(Background: This database is used to keep track of scores for students in my classes.)

So in this case, I have three tables, one has student's "codename"
(as posting their real name on the web is a no-no) and an index
(there is more data in this table, but this is all you really need to know.)
Then there's a table with the assignments, containing the assignment name,
 and an index for each assignment. Finally, there is a scores table,
 which has for each paper I get turned in, a student_id (releated to the student index)
 an act_id (related to the assignments index) and a score.

It looked something like this:
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  |
+---------------+------------+------------+-------+

So here's how the sql statement ended up:
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;

As you can see, for each activity, I need to add another left join,
but it looks exactly like the last one, thus it is easy to build through a program like php.
I hope this helps someone out.