MySQL Syntax ERROR on WHERE select

Question

I am trying to make multiple table select where ID = variabile.

Example code is below, I belieave it should work but somehow I have some error in Syntax.

SELECT c.id, c.firstname, c.surname, c.email, c.process, c.search_work, c.note,
 group_concat(DISTINCT ce.enforcement) as enfor, 
 group_concat(DISTINCT cc.city) as city
FROM candidates AS c
LEFT JOIN candidates_language AS cl ON c.id = cl.candidates_id
LEFT JOIN candidates_enforcement as ce on c.id = ce.candidates_id
LEFT JOIN candidates_city as cc on c.id = cc.candidates_id
GROUP BY c.id, c.firstname, c.surname, c.email 
WHERE c.id='8'

Fiddle: http://sqlfiddle.com/#!9/25b1b/24

ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id='

Please is there a chance someone can navigate me what I am doing wrong?

WHERE needs to be before youre GROUP BY statement :) WHERE is always before the group by as it filters the data before it is grouped. You can use HAVING after the group by to do the opposite. — Kirk Logan, Jun 02 '15 at 14:58

score 3 · Accepted Answer · answered Jun 02 '15 at 15:02

3

You have mismatched the location between WHERE and GROUP BY clause. WHERE clause should always come before GROUP BY clause since WHERE clause will filter the dataset based on the condition and on that filtered data set the grouping or GROUP BY is going to happen. So in your query it should be

WHERE c.id='8'
GROUP BY c.id, c.firstname, c.surname, c.email

answered Jun 02 '15 at 15:02

Rahul

76,197
13
71
125

Hey Rahul, thank you for answer it is correct of course and you got THUMB UP for it from me. Unfortuantely Nighthunter22 was first so to be fair I mark his answer :) Thanks for understanding – Andurit Jun 02 '15 at 15:08
2

@Andurit Being first does not always imply a higher quality answer. Personally, I think Rahul's answer does a better job of explaining the problem and the solution. – AdamMc331 Jun 02 '15 at 15:17

Nighthunter22 · Answer 2 · 2015-06-02T15:02:33.530

0

This is what you looking for:

SELECT c.id, c.firstname, c.surname, c.email, c.process, c.search_work, c.note,
 group_concat(DISTINCT ce.enforcement) as enfor, 
 group_concat(DISTINCT cc.city) as city
FROM candidates AS c
LEFT JOIN candidates_language AS cl ON c.id = cl.candidates_id
LEFT JOIN candidates_enforcement as ce on c.id = ce.candidates_id
LEFT JOIN candidates_city as cc on c.id = cc.candidates_id
WHERE c.id='8' --where comes before the group by
GROUP BY c.id, c.firstname, c.surname, c.email;

edited Jun 02 '15 at 15:02

answered Jun 02 '15 at 14:59

Nighthunter22

273
5
19

Ouch, that simple? THANKS – Andurit Jun 02 '15 at 15:02
No problem, feel free to accept the answer if it helped you ;) – Nighthunter22 Jun 02 '15 at 15:03
yea, actualy jsut waiting until it let me do it :) – Andurit Jun 02 '15 at 15:07

score 0 · Answer 3 · edited May 23 '17 at 11:43

You are getting this error because there WHERE clause should come before the GROUP BY clause, as others have mentioned. I would also like to add reference to the documentation for the SELECT syntax, which shows the order of clauses for the statement. I've summarized the order for you, and put brackets around the optional clauses:

SELECT
FROM
[WHERE]
[GROUP BY]
[HAVING]
[ORDER BY]
[LIMIT]

On a side note, the order that clauses are executed is not the same as they are written. See this question, for more information on that.

MySQL Syntax ERROR on WHERE select

3 Answers3