CODEWITHSUNDEEP
sql
hylo
hylo

Reputation: 43

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;

Upvotes: 0

Views: 565

Answers (3)

Akshey Bhat
Akshey Bhat

Reputation: 8545

SELECT student_id, m.course_id,c.name as course_name, (first_term+second_term+final) AS "Total Mark" FROM MARKS M inner join course c on m.course_id = c.course_id WHERE student_id = 1;

Use an inner join between marks and course table to get name from course table.

Upvotes: 0

Quico Llinares Llorens
Quico Llinares Llorens

Reputation: 237

SELECT student_id,
       course_id,
       name,
       (first_term+second_term+final) AS "Total  Mark"
  FROM MARKS m, Course c
  WHERE (student_id = 1) and c.course_id=m.course_id;

Upvotes: 0

trincot
trincot

Reputation: 350242

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.

Upvotes: 1

Related Questions