CODEWITHSUNDEEP
mysqlsqljoin
Chap
Chap

Reputation: 3835

SQL SELECT using a "join table"

  1. Table Student maps Student.student_name to Student.student_id
  2. Table Course maps Course.course_name to Course.course_id
  3. Table Enrollment maps Enrollment.student_id to Enrollment.course_id (I've heard this referred to as a join table.)

What is the SELECT statement that, given a student name, will return his list of course names? I think this may be some of it:

SELECT c.course_name FROM Course c 
INNER JOIN Enrollment e ON c.course_id = e.course_id 
...
WHERE s.student_name = 'Tom';

Beyond that, I'm clueless.

(This isn't homework, it's just a simplification of a work problem.)

Upvotes: 0

Views: 90

Answers (1)

Brian
Brian

Reputation: 5028

SELECT c.course_name FROM Enrollment e 
INNER JOIN Course c ON c.course_id = e.course_id 
INNER JOIN Student s ON s.student_id = e.student_id 
WHERE s.student_name = 'Tom';

also 

SELECT c.course_name 
FROM Enrollment e, Course c, Student s
WHERE c.course_id = e.course_id 
  AND s.student_id = e.student_id 
  AND s.student_name = 'Tom';

Upvotes: 1

Related Questions