mysql select with left join query

Question

我的代码是

select class.name as classname,
       class.hours as hours, 
       teachers.first_name as teacherFirstName, 
       teachers.last_name as teacherLastName 
from teachers,class 
left join class_teachers cl on class.id = cl.class_ref  
left join class_teachers cl1 on teachers.id = cl1.teachers_ref

有错误

#1054 - Unknown column 'teachers.id' in 'on clause

当我交换类(class)和老师时，错误是

#1054 - Unknown column 'class.id' in 'on clause

你们知道问题出在哪里吗？

编辑

CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `hours` int(3) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `class_teachers` (
  `class_ref` int(11) NOT NULL,
  `teachers_ref` int(11) NOT NULL
)

CREATE TABLE `teachers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
)

class_teachers 是一个中间表。这个数据库应该可以让一个类(class)有多个老师。例如体育课可以有不同的男女老师。查询应该显示带有教师姓名、类(class)名称和课时数的类(class)列表

类表

id  name     hours
1   science     3
2   math        5

教师表

id  first_name  last_name
1   Ronald      Manlapao
2   hello       goodbye
3   elise       elise
4   me          me

class_teachers 表

class_ref   teachers_ref
2                   2
1                   1

1                   2

尝试过

select c.name as classname, c.hours as hours, t.first_name as teacherFirstName, t.last_name as teacherLastName 
from teachers t, class c
left join class_teachers cl on (c.id = cl.class_ref and t.id = cl.teachers_ref)

还是一样的错误

将 class_teachers 表更新为 class_teachers_section 以包含部分。这个想法是一个类(class)可以包括不同的部分和不同的老师。例如，一年级的体育课可以包括所有部分(假设有 3 个部分)......男孩的老师对女孩来说会有所不同

select class.name as classname, 
   class.hours as hours, 
   teachers.first_name as teacherFirstName, 
   teachers.last_name as teacherLastName,
   section.name as sectionName
 from class_teachers_section cls 
 left join class on class.id = cls.class_ref  
 left join teachers on teachers.id = cls.teachers_ref
 left join section on section.id = cls.section_ref
 where section.name = 'grade 1'

Answer 1

我建议你把主表改成class_teachers，应该引用teachers和class表。

select class.name as classname, 
       class.hours as hours, 
       teachers.first_name as teacherFirstName, 
       teachers.last_name as teacherLastName 
from class_teachers cl 
left join class on class.id = cl.class_ref  
left join teachers on teachers.id = cl.teachers_ref

你的问题是你在 left join 之前使用了 from a,b，那么 left join on 子句只能找到 b .id，所以当你交换类(class)和老师时，你会得到不同的错误。

如果你真的想先交叉连接教师和类(class)，请使用 join 而不是 ,。
使用 left join 或 full outer join 将是相同的，因为您已经具备任何连接条件。

select class.name as classname,
       class.hours as hours, 
       teachers.first_name as teacherFirstName, 
       teachers.last_name as teacherLastName 
from teachers
join class 
left join class_teachers cl 
     on  class.id = cl.class_ref  
     and teachers.id = cl.teachers_ref