Re: Why oh why is this join not working?

On 11/17/19 8:15 PM, Pól Ua Laoínecháin wrote:

> 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
>
Hello Pól,

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

When I run this, I get the same result as your CTE query:

SELECT t1.class, t1.roll_number, t1.mathmark, t1.englmark
FROM
(
  SELECT class, roll_number,
  SUM(CASE WHEN subjects = 'math'
    THEN marks ELSE 0 END) AS mathmark,
  SUM(CASE WHEN subjects = 'computer'
    THEN marks ELSE 0 END) AS compmark,
  SUM(CASE WHEN subjects = 'english'
    THEN marks ELSE 0 END)  AS englmark,
  SUM(CASE WHEN subjects = 'science'
    THEN marks ELSE 0 END)  AS sciemark
  FROM resultdata
  GROUP BY class, roll_number
) 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
ORDER BY class ASC;

Regards,
Bryan

--
Postdoctoral Researcher
Georgia Cooperative Fish & Wildlife Research Unit
Warnell School of Forestry & Natural Resources
University of Georgia
Athens, GA 30606-2152