CODEWITHSUNDEEP
phpsqlpostgresql
Spatial Digger
Spatial Digger

Reputation: 1993

SQL in postgresql/php syntax error

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.

Upvotes: 2

Views: 42

Answers (2)

Rahul
Rahul

Reputation: 77934

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"

Upvotes: 0

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

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

Upvotes: 1

Related Questions