Joins on multiple tables in Postgresql

Question

I'm practising for an upcoming database exam and I'm trying to get my head around nested and multiple joins in SQL, specifically the Postgresql syntax. I want to return all the student names and department names of all students that achieved grade A.

Here's my schema.

CREATE TABLE student1 (
    student_number INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    class INTEGER NOT NULL,
    major TEXT NOT NULL
);


CREATE TABLE course1 (
    course_name TEXT NOT NULL,
    course_number TEXT NOT NULL PRIMARY KEY,
    credit_hours INTEGER NOT NULL,
    department TEXT NOT NULL
);

CREATE TABLE section1 (
    section_identifer INTEGER NOT NULL PRIMARY KEY,
    course_number TEXT NOT NULL,
    semester TEXT NOT NULL,
    year INTEGER NOT NULL,
    instructor TEXT NOT NULL,
    FOREIGN KEY (course_number) REFERENCES course1(course_number) ON DELETE CASCADE
);

CREATE TABLE grade_report1 (
    id SERIAL NOT NULL PRIMARY KEY,
    student_number INTEGER NOT NULL,
    section_identifer INTEGER NOT NULL,
    grade TEXT NOT NULL,
    FOREIGN KEY (student_number) REFERENCES student1(student_number) ON DELETE CASCADE,
    FOREIGN KEY (section_identifer) REFERENCES section1(section_identifer) ON DELETE CASCADE
);

I put together a nested statement that I thought would work:

SELECT t1.name, t3.department
FROM (student1 t1 INNER JOIN grade_report1 t2 ON t1.student_number = t2.student_number) t5
INNER JOIN (course1 t3 INNER JOIN section1 t4 ON t3.course_number = t4.course_number) t6
ON t5.section_identifer = t6.section_identifer
WHERE t2.grade = 'A';

However, this gives me the error invalid reference to FROM-clause entry for table "t1". I'm guessing it is because that is not how you are supposed to name/reference JOINS. I would like a way to JOIN all of these tables together. Thanks!

Answer 1

Remove the parentheses and fix the aliases:

SELECT s.name, c.department
FROM student1 s INNER JOIN
     grade_report1 gr
     ON gr.student_number = s.student_number INNER JOIN
     section1 sec
     ON sec.section_identifer = gr.section_identifer INNER JOIN
     course1 c 
     ON sec.course_number = c.course_number
WHERE gr.grade = 'A';

The parentheses are allowed, but they are not needed. When using parentheses (which is very, very rarely needed), they do not get separate aliases.