issue with INNER JOIN in MYSQL 6.3

im trying to join data from two tables like this:

Select portt.nameport, tool.weapen
From Portt 
Where  portt.toolsum - (    select count(*) 
                            From tool               
                            Group by nameport) >2
INNER JOIN tool on tool.nameport=portt.nameport;

but there is a 1064 error:

Error Code: 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 tool on tool.nameport=portt.nameport' at line 6

I dont get it why.

this code without the inner join works fine:

Select portt.nameport
From Portt 
Where  portt.toolsum - (    select count(*) 
                            From tool               
                            Group by nameport) >2;

Answer

First, this query:

Select portt.nameport
From Portt 
Where  portt.toolsum - (select count(*) 
                        From tool               
                        Group by nameport
                       ) > 2;

You have a scalar subquery. However, if could return more than one row, which would be an error. I am guessing you want a correlated subquery here:

select p.nameport
from Portt p
where p.toolsum - (select count(*) 
                   from tool t          
                   where t.nameport = p.nameport
                  ) > 2;

Then, JOIN is an operator in the FROM clause. The entire FROM clause needs to come before the where. So I suspect you are trying to write:

select p.nameport
from Portt p join
     tool t
     on t.nameport = p.nameport
where p.toolsum - (select count(*) 
                   from tool t          
                   where t.nameport = p.nameport
                  ) > 2;