CODEWITHSUNDEEP
sqlsqlite
Victor Rodriguez
Victor Rodriguez

Reputation: 571

SQLITE3 Inner Join Returning Too Many Tuples

I have 4 tables in sqlite3: 

students(student_id, student_name)
instructors(instructor_id, instructor_name)
courses(course_id, course_name)

and 

enrollments(enroll_id, student_id, instructor_id, course_id)

With the last 3 columns having a foreign key reference to the relevant columns in the first 3 tables

When I try to do a query that shows only the student’s name, instructor’s name and course name where an enroll_id is equal to 001 for example

SELECT students.student_name, instructors.instructor_name, courses.course_name
FROM users, instructors, courses INNER JOIN enrollments
WHERE enrollments.enroll_id = 001;

I'm getting a lot of data from all tables, but not a single tuple with just the relevant enroll data assigned to enroll_id 001. Any help is appreciated

Upvotes: 0

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax. The right way to express this query is:

SELECT s.student_full_name, i.instructor_name, c.course_name
FROM enrollments e JOIN
     instructors i
     ON e.instructor_id = i.instructor_id JOIN
     courses c
     ON e.course_id = c.course_id JOIN
     students s
     ON e.student_id = s.student_id
WHERE e.enroll_id = 001;

The use of table aliases is highly recommended. It makes the query easier to write and to read.

Upvotes: 1

svm
svm

Reputation: 388

Try this:

SELECT students.student_full_name, instructors.instructor_name, courses.course_name
    FROM users, instructors, courses, enrollments
    WHERE enrollments.enroll_id = 001
      and enrollments.student_id = users.student_id
      and enrollments.instructor_id = instructors.instructor_id
      and enrollments.course_id = courses.course_id;

Upvotes: 1

Related Questions