MySQL查询帮助请加入多个表

Question

我有这4张 table (见图)

我想做的是这个

UID | f_name |我的名字 |我的名字 |后缀 |类 |主题

000 |jan |s |gerry |空 |测试 |艺术

001 |rey |s |ronald |null |test2 |123

但是我创建的这个查询给了我这个输出

SELECT CONCAT(tbl_users.f_name, ' ', tbl_users.m_name, ' ', tbl_users.l_name, ' ', tbl_users.suffix) AS insName, tbl_classinstructor.UID, tbl_subject.subject, tbl_class.class FROM tbl_class, tbl_classinstructor LEFT OUTER JOIN tbl_users ON tbl_users.UID = tbl_classinstructor.UID, tbl_classsubject LEFT OUTER JOIN tbl_subject ON tbl_classsubject.SID = tbl_subject.SID

UID | f_name |我的名字 |我的名字 |后缀 |类 |主题

000 |jan |s |gerry |空 |测试 |艺术

000 |jan |s |gerry |空 |测试 | 123

001 |rey |s |ronald |null |test2 |arts

001 |rey |s |ronald |null |test2 |123

提前致谢

Answer 1

正确使用left join。这里应该如何

SELECT 
CONCAT(tbl_users.f_name, ' ', tbl_users.m_name, ' ', tbl_users.l_name, ' ', tbl_users.suffix) AS insName, 
tbl_classinstructor.UID, 
tbl_subject.subject, 
tbl_class.class FROM tbl_class
LEFT OUTER JOIN tbl_classinstructor on tbl_classinstructor.CID = tbl_class.CID
LEFT OUTER JOIN tbl_users ON tbl_users.UID = tbl_classinstructor.UID
LEFT OUTER JOIN tbl_classsubject on tbl_classsubject.CID = tbl_class.CID
LEFT OUTER JOIN tbl_subject ON tbl_classsubject.SID = tbl_subject.SID