SQL in postgresql/php syntax error

Question

Here's my sql

$sqltable3=
'SELECT 
"Publications"."Pub_ID", 
"Questions"."Question" 
FROM "Publications" 
LEFT JOIN "Aspect_Pub_join" ON "Publications"."Pub_ID"="Aspect_Pub_join"."Pub_ID" 
LEFT JOIN "Aspect_question_join" ON "Aspect_Pub_join"."Aspect_ID"="Aspect_question_join"."Aspect_ID" 
LEFT JOIN "Questions" ON "Aspect_question_join"."Question_ID"="Questions"."Question_ID" 
Where "Publications"."Pub_ID"=$1 
GROUP BY "Questions"."Question" 
ORDER BY "Publications"."Pub_ID" ASC';

Here's the error:

Warning: pg_query_params(): Query failed: ERROR: column "Publications.Pub_ID" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "Publications"."Pub_ID", "Questions"."Question" FROM ... ^ in ...

I'm using Publications.Pub_ID in the order by as an aggregate function, so I'm not sure where I have gone wrong? The same query ran fine in mysql (I know it is less fussy).

If I take away the group by and order by functions then the sql runs, but obviously doesn't return the desired result.

RoMEoMusTDiE · Accepted Answer · 2017-04-17 22:09:17Z

1

need to include Publications in the group by.

$sqltable3=
'SELECT 
"Publications"."Pub_ID", 
"Questions"."Question" 
FROM "Publications" 
LEFT JOIN "Aspect_Pub_join" ON "Publications"."Pub_ID"="Aspect_Pub_join"."Pub_ID" 
LEFT JOIN "Aspect_question_join" ON "Aspect_Pub_join"."Aspect_ID"="Aspect_question_join"."Aspect_ID" 
LEFT JOIN "Questions" ON "Aspect_question_join"."Question_ID"="Questions"."Question_ID" 
Where "Publications"."Pub_ID"=$1 
GROUP BY "Questions"."Question", 
"Publications"."Pub_ID" 
ORDER BY "Publications"."Pub_ID" ASC';

answered Apr 17, 2017 at 22:09

RoMEoMusTDiE

4,8341 gold badge19 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul · Accepted Answer · 2017-04-17 22:09:24Z

0

Error is pretty clear as it's asking to add another column in select list to your group by clause like below. Though don't see any reason why you need that group by since you are not using any aggregate functions

GROUP BY "Questions"."Question" , "Publications"."Pub_ID"

answered Apr 17, 2017 at 22:09

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

SQL in postgresql/php syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related