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.
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';
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"