1

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'

  • 1
    Use proper JOIN syntax..FROM x LEFT JOIN y ON..LEFT JOIN z ON.. – Mihai Aug 6 '14 at 3:58
  • You probably want either from class left join class_teachers cl on class.id = cl.class_ref left join teachers on teachers.id = cl.teachers_ref or the other way around (teachers left join class_teachers left join class) or the same as above but using inner joins. – ypercubeᵀᴹ Aug 6 '14 at 5:58
  • 1
    The section you added (problem solved using:...), add it as an answer. Not in the question. – ypercubeᵀᴹ Aug 6 '14 at 13:12
1

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.