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