Why oh why is this join not working? - Mailing list pgsql-novice

Hi all, it's late and I'm tired and I hope there's somebody out there
who can get me out of this rut! It's probably something really basic
and blindingly obvious, but I'm stumped.

All DDL and DML and SQL is available at the fiddle here:

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=6194f16306c4ebff90f56c2dac781465


My table:

CREATE TABLE resultdata
(
  class INTEGER NOT NULL,
  roll_number INTEGER NOT NULL,
  subjects VARCHAR (15) NOT NULL,
  marks INTEGER NOT NULL
);

Data - sample lines - full data (28 records) in fiddle.


INSERT INTO resultdata(class,roll_number,subjects,marks) VALUES
(8, 1, 'math', 98),
(8, 1,'english', 88),
(8, 1,'science', 96),
(8, 1,'computer', 94),... &c.

I ran this nonsense CROSS JOIN query to prove that there were no bugs
in dbfiddle.uk *_and_* that I'm not going mad! :-)

SELECT t1.class, t1.roll_number  -- Simple join - WORKS!
FROM resultdata t1
JOIN
(
  SELECT t2.class, t2.roll_number
  FROM resultdata t2
) AS t2
ON t1.class = t2.class;

class  roll_number
8        1
8        1
8        1 &c... 272 records - works fine

Then I run Query 1:

SELECT t1.class, t1.roll_number,      -- Query 1 - works!
  SUM(CASE WHEN t1.subjects = 'math'
    THEN t1.marks ELSE 0 END) AS mathmark,
  SUM(CASE WHEN t1.subjects = 'computer'
    THEN t1.marks ELSE 0 END) AS compmark,
  SUM(CASE WHEN t1.subjects = 'english'
    THEN t1.marks ELSE 0 END)  AS englmark,
  SUM(CASE WHEN t1.subjects = 'science'
    THEN t1.marks ELSE 0 END)  AS sciemark
FROM resultdata t1
GROUP BY t1.class, t1.roll_number;

Works.

Then Query 2:

SELECT class, MAX(marks) AS maxmark  -- Query 2 - works!
FROM resultdata
WHERE subjects = 'english'
GROUP BY class;

Works.

BUT, when I try and run this (JOINING the two tables above):

SELECT t1.class, t1.roll_number,
  SUM(CASE WHEN t1.subjects = 'math'
    THEN t1.marks ELSE 0 END) AS mathmark,
  SUM(CASE WHEN t1.subjects = 'computer'
    THEN t1.marks ELSE 0 END) AS compmark,
  SUM(CASE WHEN t1.subjects = 'english'
    THEN t1.marks ELSE 0 END)  AS englmark,
  SUM(CASE WHEN t1.subjects = 'science'
    THEN t1.marks ELSE 0 END)  AS sciemark
FROM resultdata t1
GROUP BY t1.class, t1.roll_number
JOIN    <<<<<==== Fails here
(
  SELECT class, MAX(marks) AS maxmark
  FROM resultdata
  WHERE subjects = 'english'
  GROUP BY class
) AS t2
ON t1.class = t2.class AND
   t1.englmark = t2.maxmark;

Result is: ERROR: syntax error at or near "JOIN" LINE 12: JOIN

I've tried put every variation that I can think of to alias the first
table - brackets... the whole chebang - I can get nothing to work!

As a final note, when I use CTEs, it works fine. However, I have to
get this code working on a MySQL 5.7 box also, but I'd be interested
to know why I can't perform a simple join using PostgreSQL.

Should you require any further information, please don't hesitate to
contact me. Any helpful URLs or SQL references appreciated.

TIA and rgs, Pól...

WITH cte1 AS .   -- <<<<<<<<<< This whole CTE with JOIN at end works
fine also - produces correct result!
  SELECT t1.class, t1.roll_number,      -- Query 1 - works!
  SUM(CASE WHEN t1.subjects = 'math'
    THEN t1.marks ELSE 0 END) AS mathmark,
  SUM(CASE WHEN t1.subjects = 'computer'
    THEN t1.marks ELSE 0 END) AS compmark,
  SUM(CASE WHEN t1.subjects = 'english'
    THEN t1.marks ELSE 0 END)  AS englmark,
  SUM(CASE WHEN t1.subjects = 'science'
    THEN t1.marks ELSE 0 END)  AS sciemark
  FROM resultdata t1
  GROUP BY t1.class, t1.roll_number
),
cte2 AS
(
  SELECT class, MAX(marks) AS maxmark
  FROM resultdata
  WHERE subjects = 'english'
  GROUP BY class
)
SELECT t1.class, t1.roll_number, t1.mathmark, t1.englmark
FROM cte1 t1
JOIN cte2 t2
  ON t1.class = t2.class AND
     t1.englmark = t2.maxmark
ORDER BY class ASC;