Search code examples
mysqlsqlmysql-workbenchrdbms

How to return an output table from three separate tables that has one row from each input table in MySQl Workbench?

The question asked is as following:

Write a query that shows a student's transcript. The query should output columns:

  1. Student's Name
  2. Course Title
  3. Numerical Grade

For extra credit list the grade as a letter instead of a number.

The 3 relevant tables are:

registration which contains the columns

Section_ID / Student_ID / Grade(this is numerical using the 4 point GPA system with no decimals)

student which contains the columns

ID / Name / Email

course which contains the columns

ID / Title / Description / Units

The best I could come up with was:

select name, Grade, Title 
from SELECT * FROM 
(
    student 
    join registration 
    on registration.Student_ID = student.ID
    join course on course.ID = registration.Section_ID
) as transcript group by student ;

This gave me 1064 syntax error, and I'm not sure why. Trying a nested select statement with just 2 of the tables in question worked, don't know why the select from the triple join is rejected.

As for the extra credit portion, I'm not sure what to do besides running an update table statement on the grades column and setting 1,2,3,4 to D, C, B, A.

Solution

  • Your select statement has some syntax issue. The query should be as below. You need to use JOIN properly to establish relation between two tables using Relational Key column.

    SELECT name, Grade, Title 
FROM Student A
INNER JOIN registration B B.Student_ID = A.ID
INNER JOIN Course C C.ID = B.Section_ID

    To get Results per student, Please apply a filter end of the script as-

    WHERE A.ID = N --Put Student ID Here.