我有4个表。
1. Course 2. Semester 3. AssignTeacher 4. Teacher.
Course has id, name, code.
Semester has id, name, course_id.
AssignTeacher has id,course_id,teacher_id.
Teacher has id, name.
现在我想查找课程代码、课程名称、学期名称和教师名称请参阅上表。其中teachers.name应与AssignTeacher表中具有id的教师一起提供。现在有了课程表,当该课程没有教师id而不是显示null值时,我可以在查询中设置一些默认值,比如"None"吗?
这是我写的查询,尽管我没有得到我所期望的:
SELECT courses.code, courses.name, semesters.name,teachers.name
FROM semesters
JOIN courses on courses.semester_id = semesters.id
JOIN assign_teachers on assign_teachers.course_id=courses.id
JOIN teachers on assign_teachers.id=teachers.id
当然,您应该使用COALESCE()
,它将null替换为所需值:
SELECT courses.code, courses.name, semesters.name,COALESCE(teachers.name,'None') as teacher_name
from semesters join courses on courses.semester_id = semesters.id
join assign_teachers on assign_teachers.course_id=courses.id
join teachers on assign_teachers.id=teachers.id
但奇怪的是,当没有teacher.id时,你会得到null值,因为你使用的是普通联接,所以应该过滤这个记录。我认为你应该使用LEFT联接:
SELECT courses.code, courses.name, semesters.name,COALESCE(teachers.name,'None') as teacher_name
from semesters join courses on courses.semester_id = semesters.id
left join assign_teachers on assign_teachers.course_id=courses.id
left join teachers on assign_teachers.id=teachers.id
如果我理解正确,请将选择列表更改为
COALESCE(teachers.name, 'None')