Why oh why is this join not working?
От | Pól Ua Laoínecháin |
---|---|
Тема | Why oh why is this join not working? |
Дата | |
Msg-id | CAF4RT5R+WkzVheTEeTC7S87AvSKvz3VUvvTrLfFhKwucM_gQMA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Why oh why is this join not working?
Re: Why oh why is this join not working? Re: Why oh why is this join not working? |
Список | pgsql-novice |
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;
В списке pgsql-novice по дате отправления: