help with basic SQL QUERY

Question

i have created a few tables for a schoool project. The tables of interest are

                //table courses
        CREATE TABLE courses(courseId SERIAL PRIMARY KEY, facultyId REFERENCES faculties(facultyId), courseName TEXT);

                //table weights
    CREATE TABLE weights(weightId SERIAL PRIMARY KEY, weightName TEXT, weight INTEGER);

//table subjects
CREATE TABLE subjects(subjectId SERIAL PRIMARY KEY, subjectName TEXT);
        //table weights_subjects_courses
    CREATE TABLE weights_subjects_courses(courseId integer REFERENCES courses(courseId), weightId integer REFERENCES weights(weightId), subjectId integer REFERENCES subjects(subjectId)

The problem comes when i try the following query

SELECT * FROM courses, subjects, weights WHERE courses.courseId= weights_subjects_courses.courseId AND subjects.subjectId= weights_subjects_courses.subjectId AND weights.weightId= weights_subjects_courses.weightId ORDER BY courseName;

i get this error SQL error:

ERROR:  missing FROM-clause entry for table "weights_subjects_courses"
LINE 1: ...ourses, subjects, weights WHERE courses.courseId= weights_su...
                                                             ^

Thanks in advance

Shan Plourde · Accepted Answer · 2011-03-06 06:30:28Z

2

You have weights_subjects_courses in your where clause, but it's not in the from clause. Whenever you join tables in your where clause, you need to also include them in your from clause.

So just add weights_subjects_courses into your from clause to fix that error.

answered Mar 6, 2011 at 6:30

Shan Plourde

8,7362 gold badges33 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dr Deo Over a year ago

"Whenever you join tables in your where clause, you need to also include them in your from clause." Thanks :)

Shan Plourde Over a year ago

anytime, I imagine that was just an oversight ;)

Collectives™ on Stack Overflow

help with basic SQL QUERY

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related