Re: Why oh why is this join not working?

On Mon, 18 Nov 2019 01:15:36 +0000
Pól Ua Laoínecháin <linehanp@tcd.ie> wrote:

Hi,

What does:
    EXPLAIN ANALYZE <query that fails>
returns?

Jean-Yves

> 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;
>
>