MySql Inner Join with WHERE clause

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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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'

Method 2

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'

Method 3

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'

Method 4

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'

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating