多表关联查询 AND “LEFT JOIN ON“

一.多表关联查询：

二.已知以上6表，查询教室名称为“东九楼A413”第三周星期五的“教师姓名”、“职工号”、“起始节次”、“结束节次”、“课程名称”。

1.通过教室名称先查到对应的教室编号：

SELECT j.JSMC AS 教室名称,j.JSBH AS 教室编号 FROM T_JS j WHERE j.JSMC = 东九楼A413;

2.根据教室编号查询“东九楼A413第三周星期五的起始周次、结束周次、开始节次、结束节次、职工号、姓名、课程名称”：

SELECT DISTINCT
	jh.QSZC 起始周次,
	jh.JSZC 结束周次,
	jh.QSJC 开始节次,
	jh.JSJC 结束节次,
	j.ZGH 职工号,
	j.XM 姓名,
	kc.KCMC 课程名称, 
FROM	
    T_HKJCSJ_KT_SKJH jh,
	T_HKJCSJ_JZGJBXX j,
	T_HKJCSJ_KT_JS js,
	T_HKJCSJ_JS s,
	T_HKJCSJ_KT kt,
	T_HKJCSJ_KC kc 
WHERE
	jh.KTBH = kt.KTBH 
	AND jh.JSBH = s.JSBH 
	AND jh.SKJHBH = js.SKJHBH 
	AND kt.KCBH = kc.KCBH 
	AND j.ZGH = js.JGH 
	AND s.JSBH = D09413 
	AND jh.XQ = 5 
	AND jh.JSZC > 2 
	AND jh.QSZC < 4;

3.左连接：

SELECT DISTINCT
	jh.QSZC 起始周次,
	jh.JSZC 结束周次,
	jh.QSJC 起始节次,
	jh.JSJC 结束节次,
	j.ZGH 职工号,
	j.XM 姓名,
	kc.KCMC 课程名称,
FROM
	T_HKJCSJ_KT_SKJH jh
	LEFT JOIN T_HKJCSJ_KT kt ON jh.KTBH = kt.KTBH
	LEFT JOIN T_HKJCSJ_JS s ON jh.JSBH = s.JSBH
	LEFT JOIN T_HKJCSJ_KT_JS js ON jh.SKJHBH = js.SKJHBH
	LEFT JOIN T_HKJCSJ_KC kc ON kc.KCBH = kt.KCBH
	LEFT JOIN T_HKJCSJ_JZGJBXX j ON j.ZGH = js.JGH 
WHERE
	s.JSBH = D09413 
	AND jh.XQ = 5 
	AND jh.JSZC > 2 
	AND jh.QSZC < 4;