user4514473 user4514473 - 2 years ago 80
SQL Question

How to get each course name in a grades table where I only have the id for course?

I have a Grades table where I have the following fields:

-STUDENT_ID

-COURSE_ID

-FIRST_TERM

-SECOND_TERM

-FINAL

And a Course table:

-COURSE_ID

-NAME

-DEPARTMENT_ID

I'm trying to get all the grades for a particular student with grades for each course specified, I was wondering how do I get the name of each course?

This is how I get the grades but I want to include the course name also:

SELECT student_id,
course_id,
(first_term+second_term+final) AS "Total Mark"
FROM MARKS
WHERE student_id = 1;

Answer Source

You can use this query:

SELECT     s.student_id,
           s.course_id,
           c.course_name,
           (s.first_term+s.second_term+s.final) AS "Total  Mark"
FROM       marks s
INNER JOIN course c ON c.course_id = s.course_id
WHERE      s.student_id = 1

Make sure to prefix field names with table names when they are used in both tables (like for course_id). I have prefixed all fields with table aliases.

Table aliases are like short names for tables and you define them right after the table name in the FROM clause.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download