根据其他表选择数据

I'm still newbie with database and mysql stuff, i want to learn how to use JOINs.I'm sorry i just have no idea to put this case on words. I hope all of You can understand by looking these data. Here's the tables with records:

我仍然是数据库和mysql的新手,我想学习如何使用JOIN。我很抱歉我只是不知道把这个案例放在单词上。我希望你们所有人都可以通过查看这些数据来理解。这是包含记录的表格:

Table student 
student_id | student_name 
-------------------------
1            Ana
2            Billy
3            Connor

Table comp 
comp_id | subj_id | comp_name  
--------------------------
1         24         Run       
2         24         Swim       
3         24         Jump
4         25         Eat    

Table comp_mark 
semester | subj_id | student_id | comp_id | mark
-------------------------------------------------
1          24        1            1         7
1          24        1            2         4
1          24        1            3         6
1          24        2            1         4
1          24        2            2         8
1          24        3            1         9

I'm expecting the result something like this from the query selecting comp_mark table(update):

我期待从查询中选择comp_mark表(更新)的结果:

student_name | semester | subject_id | comp_id  | mark
-------------------------------------------------------
Connor        1          24           1          9
Connor        null       24           2          null
Connor        null       24           3          null

is there anyone can help me with this? Thank a ton

有没有人可以帮我这个?谢天谢地

UPDATE

I've tried this (Guillaume Poussel's query) and ORDER it by student_name :

我试过这个(Guillaume Poussel的查询)并通过student_name对它进行排序:

 SELECT s.student_name, cm.semester, c.subj_id AS subject_id, c.comp_id, cm.mark
 FROM student s
 CROSS JOIN comp c
 LEFT JOIN comp_mark cm ON s.student_id = cm.student_id
 AND cm.subj_id = c.subj_id
 AND cm.comp_id = c.comp_id
 ORDER BY s.student_name

The results: 根据其他表选择数据

2 个解决方案

#1

Try this one:

试试这个:

SELECT s.student_name, cm.semester, c.subj_id AS subject_id, c.comp_id, cm.mark
FROM student s
CROSS JOIN comp c
LEFT JOIN comp_mark cm ON s.student_id = cm.student_id AND cm.subj_id = c.subj_id AND cm.comp_id = c.comp_id

#2

Think you need to add a cross join to get all the possible values from comp. Something like this:-

认为您需要添加交叉连接以从comp获取所有可能的值。像这样: -

 SELECT s.student_name, c.subj_id, s.student_id, c.comp_id, cm.mark 
 FROM student s
 CROSS JOIN comp c
 LEFT OUTER JOIN comp_mark cm 
 ON s.student_id = cm.student_id 
 AND cm.semester = '1' 
 AND cm.subj_id = '1'

#1