Okay, at this point I just don't know what to do next. I just started learning joins in mySQL and what I'm trying to do is to join 2 tables through it's junction table (Many-to-Many) relationship. But the examples in http://www.w3schools.com/sql/sql_join_left.asp which I'm following didn't say anything about MANY-TO-MANY tables.
好的,此时我只是不知道接下来该做什么。我刚开始学习mySQL中的连接,我想要做的是通过它的连接表(多对多)关系连接2个表。但是我正在关注的http://www.w3schools.com/sql/sql_join_left.asp中的示例没有说明MANY-TO-MANY表。
I have 3 tables.
我有3张桌子。
1.) curriculum
2.) curriculumsubjects --the junction table
2.)课程主题 - 联结表
3.) subject
curriculum
id PK
name
description
yearLevel
syStart
syEnd
curriculumsubjects --junction table
课程主题 - 连接表
id PK
curriculumId FK
subjectCode FK
subject
code PK
name
yrLevel
description
Desired result is to show the subject code, subject name, subject description, subject yearlevel if supplied or if given the curriculum name and curriculum year level on procedure call.
期望的结果是显示主题代码,主题名称,主题描述,主题年级(如果提供)或者如果在程序调用中给出课程名称和课程年级。
So here's what I did.
所以这就是我做的。
CREATE PROCEDURE `getCurriculumSubjects` (IN p_CurcName varchar(50),IN p_yrLevel varchar(50))
BEGIN
SELECT `subject`.`code`,`subject`.`name`,`subject`.yrLevel, `subject`.description
FROM `subject`
LEFT OUTER JOIN curriculumsubjects
ON `subject`.`code` = curriculumsubjects.subjectCode
LEFT OUTER JOIN curriculum
ON curriculum.id = curriculumsubjects.curriculumId
WHERE curriculumsubjects.id = (SELECT id FROM curriculum WHERE `name` = p_CurcName AND yearLevel = p_yrLevel);
END
Currently there's one record on the curriculum table and 2 subjects record on subject table.
目前课程表上有一个记录,主题表上有2个主题记录。
CURRICULUM Table Screenshot
CURRICULUM表截图
SUBJECT Table Screenshot
主题表截图
CURRICULUMSUBJECT Table Screenshot
CURRICULUMSUBJECT表截图
But when I ran the script, I get nothing.
但是当我运行脚本时,我什么都没得到。
I mean, how do I properly use left join to fix this problem? Can I join more tables instead of just 2?
我的意思是,如何正确使用左连接来解决这个问题?我可以加入更多的桌子而不只是2吗?
I'd appreciate any help.
我很感激任何帮助。
Thanks in advance.
提前致谢。
1 个解决方案
#1
1
I think this achieves what you are asking:
我认为这可以实现你所要求的:
select s.code, s.name, s.description, s.yrLevel
from subject s
inner join curriculumsubject cs on s.code = cs.subjectCode
inner join curriculum c on cs.curriculumId = c.ID
where c.name = p_CurcName and c.yearLevel = p_yrLevel
I don't think left join is appropriate, unless you want to return empty values for the subject, when the parameter values exist in curriculum, but there's no link to subject. If you want to return rows only where there's a matching subject, then inner join is the correct solution.
我不认为左连接是合适的,除非您想要在主题中存在参数值时为主题返回空值,但是没有指向主题的链接。如果只想在匹配主题的位置返回行,则内连接是正确的解决方案。
#1
1
I think this achieves what you are asking:
我认为这可以实现你所要求的:
select s.code, s.name, s.description, s.yrLevel
from subject s
inner join curriculumsubject cs on s.code = cs.subjectCode
inner join curriculum c on cs.curriculumId = c.ID
where c.name = p_CurcName and c.yearLevel = p_yrLevel
I don't think left join is appropriate, unless you want to return empty values for the subject, when the parameter values exist in curriculum, but there's no link to subject. If you want to return rows only where there's a matching subject, then inner join is the correct solution.
我不认为左连接是合适的,除非您想要在主题中存在参数值时为主题返回空值,但是没有指向主题的链接。如果只想在匹配主题的位置返回行,则内连接是正确的解决方案。