MySQL select with left join query

MySQLselect

I have following code.

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

I got following error.

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

When I interchanged class and teachers the error was:

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

Do you guys have an idea whats the problem?

edit

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 is an intermediary table. This database should make possible that a class should have multiple teachers. For example PE class can have separate teachers for boys and girls. The query should show list of class with teachers names, class name and number of class hours

class table

id  name     hours
1   science     3
2   math        5

teachers table

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

class_teachers table

class_ref   teachers_ref
2                   2
1                   1
1                   2

I tried the following:

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)

But still the got same error.

problem solved using:

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

edit – moved to separate answer by OP


updated class_teachers table to class_teachers_section to include section. the idea is a class can include different sections with different teachers. for example a grade 1 PE class can include all sections (lets pretend 3 sections) … teacher for boys will be different for girls

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'

Best Answer

I updated class_teachers table to class_teachers_section to include section. The idea is a class can include different sections with different teachers. For example a grade 1 PE class can include all sections; the teacher for boys will be different from that for girls.

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'
Related Question