我需要基于一些先前表的输出在几个表之间创建一个联接查询。我正在尝试使用如下情况时的语句,但显示错误!任何帮助都值得赞赏
SELECT completion.userid, completion. coursemoduleid, completion. timemodified,
module.course, user. idnumber as student_id, m.name as module_name, activity.name as activity_name
FROM `mdl_course_modules_completion` as completion
join mdl_course_modules as module
on completion. coursemoduleid = module.id
join mdl_user as user on user.id = completion.userid
join mdl_modules as m on completion. coursemoduleid = m.id
join
CASE WHEN module_name = 'assign'THEN 'mdl_assign'
WHEN module_name = 'assignment' THEN 'mdl_assignment'
ELSE "quiz"
END AS activity
on activity.id = m.id LIMIT 0, 30
最佳答案
没有“条件连接”这样的东西。相反,您可以使用两个LEFT JOIN和COALESCE():
SELECT completion.userid,
...
COALESCE(activity1.name, activity2.name) as activity_name -- SELECT first non NULL value
FROM `mdl_course_modules_completion` as completion
...
LEFT JOIN mdl_assign activity1
ON activity1.id = m.id -- JOIN condition
AND module_name = 'assign' -- CASE condition
LEFT JOIN mdl_assignment activity2
ON activity2.id = m.id -- JOIN condition
AND module_name = 'assignment' -- CASE condition