Re: Why oh why is this join not working?

Hi Bryan, and thanks for your interest in my problem.

> I think you just need parentheses around the first subquery, and to put
> both subqueries in the FROM list of an outer SELECT.

Yes, I could do that! However, if you check out my reply to David
Rowley in the same thread, you'll see that I've got much further.

Fiddle here:
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=5aaa2fde61da3b70521f5e1bc29a047e

With the query:

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 AS t1
JOIN
(
  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        -- Error occurs here.
GROUP BY t1.class, t1.roll_number
ORDER BY class, englmark DESC;

The error occurs way down the query at line 19. Error message:

ERROR: column t1.englmark does not exist LINE 19: AND t1.englmark = t2.maxmark

Now, I can refer to t1.class in the ON clause, but *_not_* to
t1.englmark. To me this makes no sense - if I can refer to one, I
should be able to refer to the other?

I could have taken the nested subquery strategy but a) I think the
query would be more elegant without it and b) (something I'll check up
on), I'm wondering if more levels of subqueries might have performance
implications? Finally, c) As mentioned above, if I can refer to
t1.class, I should be able to refer to t1.englmark!

Thanks again for your input, rgs,

Pól...


> Bryan