Error for INNER JOIN with GROUP BY and WHERE clause

Question

I've got these tables

student:

id  Name
-------------------
1   john
2   carlos
3   zoya
4   arab
5   amir

and,

email:

id      email   student_id
--------------------------
1   a@mail.com      1
2   b@mail.com      2
3   c@mail.com      2
4   d@mail.com      3
5   e@mail.com      4

I'm using this query and it's getting Using sql error on query line 4,

SELECT * FROM student 
INNER JOIN email 
ON student.id = email.student_id 
GROUP BY student.id
WHERE student.id = 2

I don't have much experience in SQL.

@MKhalidJunaid Thanks man once again . but i doubt that i've heard once that `WHERE` clause should be put at last of the query .. . — Sohan Patel, Aug 25 '14 at 19:43
@user3710243 you have to follow the syntax for your query as mentioned in [*`Docs`*](http://dev.mysql.com/doc/refman/5.0/en/select.html) — M Khalid Junaid, Aug 25 '14 at 19:48
Do you really need `GROUP BY`? Can a student have multiple entries in the `email` table? If so, are you OK with it picking one of them at random? — Barmar, Aug 25 '14 at 20:02

score 0 · Answer 1 · answered Aug 25 '14 at 19:42

0

Firstly, GROUP BY always goes after the WHERE clause.
Secondly, if you are using the GROUP BY clause you should either use aggregate functions for the fields, that are not contained in it and still are present in the SELECT clause, or GROUP BY the whole pack of columns contained in the SELECT.

answered Aug 25 '14 at 19:42

potashin

44,205
11
83
107

but as mentioned http://stackoverflow.com/questions/1392479/using-where-and-inner-join-in-mysql. i was using `WHERE` clause at last. – Sohan Patel Aug 25 '14 at 19:53
1

@user3710243 : It is not right, use link that *M Khalid Junaid* has provided to check the correct order of the clauses. – potashin Aug 25 '14 at 19:56

Error for INNER JOIN with GROUP BY and WHERE clause

1 Answers1

Linked