CODEWITHSUNDEEP
mysqlsqlselectforeign-keys
Jarle Langemyr
Jarle Langemyr

Reputation: 31

SQL view all courses for one student

I have 3 tables

table_student
ID, firstname, lastname

table_courses
ID, course_name

table_student_course
ID, student_ID, course_ID, date_taken

All I want to do is list all courses by course_name and date_taken by student with ID=1

Anyone please?

Upvotes: 3

Views: 2554

Answers (3)

B B
B B

Reputation: 1950

By using Inner Join on the tables "table_courses" & "table_student_course" we selects all rows from both tables as long as there is a match between the columns in both tables making sure the ids are the same. If the condition is met( student_id=1) then the query will return what is expected.

SELECT course_name, date_taken
FROM table_courses c INNER JOIN table_student_course sc ON c.id = sc.course_id
WHERE sc.student_ID = 1
ORDER BY course_name

Upvotes: 1

Mahesh
Mahesh

Reputation: 8892

You need to use the JOIN on the table_courses and table_student_course tables and then apply the Order By on cource_name to sort out by course name.And for selecting the particular student apply the Where clause as filter.

  SELECT 
      t.course_name,
      tsc.date_taken 
  FROM 
      table_courses t INNER JOIN table_student_course tsc
          ON t.ID = tsc.course_ID
  WHERE
      tsc.student_ID = 1
  ORDER BY 
       t.course_name

If you also want to get the students detail from the query then you need to join the 3 tables like below,

SELECT s.firstname, s.lastname, c.course_name, sc.date_taken 
FROM table_courses c 
INNER JOIN table_student_course sc ON c.ID = sc.course_ID
INNER JOIN table_student s ON sc.student_ID = s.ID
WHERE sc.student_ID = 1
ORDER BY c.course_name

Upvotes: 6

Matt
Matt

Reputation: 15071

SELECT s.firstname, s.lastname, c.course_name, sc.date_taken 
FROM table_courses c 
INNER JOIN table_student_course sc ON c.ID = sc.course_ID
INNER JOIN table_student s ON sc.student_ID = s.ID
WHERE sc.student_ID = 1
ORDER BY c.course_name

Upvotes: 1

Related Questions