MySql Inner Join with WHERE clause

Question

Here is my code:

 SELECT table1.f_id  FROM table1 WHERE table1.f_com_id = '430' AND      
 table1.f_status = 'Submitted' 
 INNER JOIN table2
 ON table2.f_id = table1.f_id
 where table2.f_type = 'InProcess'

I need information from table1 as all the id associated with f_com_id as 430 and status as submitted and the type should be only in process which is stored in other table(table2)

f_id is p_key and f_key in both the tables.
But this giving me errors, I think I am placing the WHERE clause wrong, how to fix it.?

Error msg: #1064 - 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 'INNER JOIN table2 ON table2.f_id = ' at line 2

Almost 80k views, ~12 votes on question and answer with 25+ votes. Surely not too localized. — Himanshu, Feb 15 '18 at 06:15
A very popular syntax error, yes. Possibly not too localized, but off-topic nonetheless, like the other syntax errors. — Félix Adriyel Gagnon-Grenier, Feb 27 '18 at 14:51

Himanshu · Accepted Answer · 2012-09-11T07:23:32.877

94

Yes you are right. You have placed WHERE clause wrong. You can only use one WHERE clause in single query so try AND for multiple conditions like this:

 SELECT table1.f_id  FROM table1 
   INNER JOIN table2
     ON table2.f_id = table1.f_id
 WHERE table2.f_type = 'InProcess'
   AND f_com_id = '430'
   AND f_status = 'Submitted'

edited Sep 11 '12 at 07:23

answered Sep 11 '12 at 07:16

Himanshu

31,810
31
111
133

1

thnx a lot..it worked for me.. but what i feel is JOIN operations are always a costier approach, can we do this some other way whithout using join opertaion. if possible? – Aditya Kumar Sep 11 '12 at 08:48
1

@AdityaKumar Join is better approach than others (e.g. sub-query etc.). Prefer join . See [this SQLFiddle](http://sqlfiddle.com/#!3/3db23/3). – Himanshu Sep 11 '12 at 08:52

score 12 · Answer 2 · answered Sep 11 '12 at 07:21

12

1. Change the INNER JOIN before the WHERE clause.
2. You have two WHEREs which is not allowed.

Try this:

SELECT table1.f_id FROM table1
  INNER JOIN table2 
     ON (table2.f_id = table1.f_id AND table2.f_type = 'InProcess') 
   WHERE table1.f_com_id = '430' AND table1.f_status = 'Submitted'

answered Sep 11 '12 at 07:21

Mistdemon

648
5
8

strangely, the parentheses resolved my issue – gawkface Apr 26 '21 at 16:30

score 1 · Answer 3 · answered Sep 11 '12 at 07:17

1

You could only write one where clause.

 SELECT table1.f_id  FROM table1
 INNER JOIN table2
 ON table2.f_id = table1.f_id
 where table1.f_com_id = '430' AND      
 table1.f_status = 'Submitted' AND table2.f_type = 'InProcess'

answered Sep 11 '12 at 07:17

xdazz

158,678
38
247
274

score 0 · Answer 4 · edited Feb 27 '18 at 14:54

0

You are using two WHERE clauses but only one is allowed. Use it like this:

SELECT table1.f_id FROM table1
INNER JOIN table2 ON table2.f_id = table1.f_id
WHERE
  table1.f_com_id = '430'
  AND table1.f_status = 'Submitted'
  AND table2.f_type = 'InProcess'

edited Feb 27 '18 at 14:54

Félix Adriyel Gagnon-Grenier

8,422
10
52
65

answered Sep 11 '12 at 07:22

AboQutiesh

1,696
2
9
14

MySql Inner Join with WHERE clause

4 Answers4

Linked