SQL in postgresql/php syntax error

2
votes

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.

2
phpsqlpostgresql

2 Answers

1
votes

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';
0
votes

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"