1

I'm trying to display the information I have in these columns and it keeps telling me that the first_name column in my professors table is unknown. Does anyone know why? Please help me, I've tried everything. Thanks.

Here's the query:

SELECT courses.name, sections.section_name, professors.first_name, professors.last_name, classrooms.room
FROM courses
INNER JOIN course_section ON sections.id = course_section.section_id
INNER JOIN professor_course ON professors.id = professor_course.professor_id
WHERE courses.id = 1;
PressingOnAlways
  • 11,948
  • 6
  • 32
  • 59
jayllellis
  • 13
  • 1
  • 3

4 Answers4

2

The error is because you haven't included sections in any from or join. You have the same thing going on with professors.

Personally, I prefer the readability of implied inner joins in this situation. A DBA may disagree, but as a programmer I find this easier to figure out what's going on.

SELECT courses.name, sections.section_name, professors.first_name, professors.last_name, classrooms.room 
FROM courses, course_section, sections, professors, professor_course, classrooms
WHERE sections.id = course_section.section_id
AND professors.id = professor_course.professor_id 
AND -- classrooms???
AND courses.id = 1;

Not knowing your DB structure, you can probably get what you are looking for with less table joins.

AndrewR
  • 6,668
  • 1
  • 24
  • 38
  • Thanks, I don't get the original error anymore but I get this one: Unknown column 'course_classroom.classroom_id' in 'where clause'. Here's my query: SELECT courses.name, sections.section_number, professors.first_name, professors.last_name, classrooms.room FROM courses, course_section, sections, professors, professor_course, classrooms WHERE sections.id = course_section.section_id AND professors.id = professor_course.professor_id AND classrooms.id = course_classroom.classroom_id AND courses.id = 1; – jayllellis Apr 15 '11 at 14:53
  • Figured it out! Thanks a lot! Your query helped me sort my way out! – jayllellis Apr 17 '11 at 09:25
1

You're not joining the professors table in your FROM clause, you only have professor_course.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
0

Recheck the database, you probably misspelled the column in there.

johnlemon
  • 20,761
  • 42
  • 119
  • 178
0

u have completely wrong Query..

as u r selecting from different tables( SELECT clause) and there is no such table on FROM or JOIN clause

the tables you need : courses, sections, professors,classrooms

in

 INNER JOIN course_section cs ON sections.id = cs.section_id

u must include table sections before writing above condition

same thing for

    INNER JOIN professor_course pc ON professors.id = pc.professor_id

u must include table professors before writing above condition

i think your Query should be

Note: better to use alias for tables

ALL D BEST

Community
  • 1
  • 1
xkeshav
  • 53,360
  • 44
  • 177
  • 245