Re: Why oh why is this join not working?

On Mon, 18 Nov 2019 01:15:36 +0000
P=C3=B3l Ua Lao=C3=ADnech=C3=A1in <[email protected]> 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.
>=20
> All DDL and DML and SQL is available at the fiddle here:
>=20
> https://dbfiddle.uk/?rdbms=3Dpostgres_12&fiddle=3D6194f16306c4ebff90f56c2=
dac781465
>=20
>=20
> My table:
>=20
> CREATE TABLE resultdata
> (
>   class INTEGER NOT NULL,
>   roll_number INTEGER NOT NULL,
>   subjects VARCHAR (15) NOT NULL,
>   marks INTEGER NOT NULL
> );
>=20
> Data - sample lines - full data (28 records) in fiddle.
>=20
>=20
> 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.
>=20
> I ran this nonsense CROSS JOIN query to prove that there were no bugs
> in dbfiddle.uk *_and_* that I'm not going mad! :-)
>=20
> 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 =3D t2.class;
>=20
> class  roll_number
> 8        1
> 8        1
> 8        1 &c... 272 records - works fine
>=20
> Then I run Query 1:
>=20
> SELECT t1.class, t1.roll_number,      -- Query 1 - works!
>   SUM(CASE WHEN t1.subjects =3D 'math'
>     THEN t1.marks ELSE 0 END) AS mathmark,
>   SUM(CASE WHEN t1.subjects =3D 'computer'
>     THEN t1.marks ELSE 0 END) AS compmark,
>   SUM(CASE WHEN t1.subjects =3D 'english'
>     THEN t1.marks ELSE 0 END)  AS englmark,
>   SUM(CASE WHEN t1.subjects =3D 'science'
>     THEN t1.marks ELSE 0 END)  AS sciemark
> FROM resultdata t1
> GROUP BY t1.class, t1.roll_number;
>=20
> Works.
>=20
> Then Query 2:
>=20
> SELECT class, MAX(marks) AS maxmark  -- Query 2 - works!
> FROM resultdata
> WHERE subjects =3D 'english'
> GROUP BY class;
>=20
> Works.
>=20
> BUT, when I try and run this (JOINING the two tables above):
>=20
> SELECT t1.class, t1.roll_number,
>   SUM(CASE WHEN t1.subjects =3D 'math'
>     THEN t1.marks ELSE 0 END) AS mathmark,
>   SUM(CASE WHEN t1.subjects =3D 'computer'
>     THEN t1.marks ELSE 0 END) AS compmark,
>   SUM(CASE WHEN t1.subjects =3D 'english'
>     THEN t1.marks ELSE 0 END)  AS englmark,
>   SUM(CASE WHEN t1.subjects =3D 'science'
>     THEN t1.marks ELSE 0 END)  AS sciemark
> FROM resultdata t1
> GROUP BY t1.class, t1.roll_number
> JOIN    <<<<<=3D=3D=3D=3D Fails here
> (
>   SELECT class, MAX(marks) AS maxmark
>   FROM resultdata
>   WHERE subjects =3D 'english'
>   GROUP BY class
> ) AS t2
> ON t1.class =3D t2.class AND
>    t1.englmark =3D t2.maxmark;
>=20
> Result is: ERROR: syntax error at or near "JOIN" LINE 12: JOIN
>=20
> 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!
>=20
> 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.
>=20
> Should you require any further information, please don't hesitate to
> contact me. Any helpful URLs or SQL references appreciated.
>=20
> TIA and rgs, P=C3=B3l...
>=20
> 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 =3D 'math'
>     THEN t1.marks ELSE 0 END) AS mathmark,
>   SUM(CASE WHEN t1.subjects =3D 'computer'
>     THEN t1.marks ELSE 0 END) AS compmark,
>   SUM(CASE WHEN t1.subjects =3D 'english'
>     THEN t1.marks ELSE 0 END)  AS englmark,
>   SUM(CASE WHEN t1.subjects =3D '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 =3D 'english'
>   GROUP BY class
> )
> SELECT t1.class, t1.roll_number, t1.mathmark, t1.englmark
> FROM cte1 t1
> JOIN cte2 t2
>   ON t1.class =3D t2.class AND
>      t1.englmark =3D t2.maxmark
> ORDER BY class ASC;
>=20
>=20