Incorrect SQL Syntax when using JOIN [duplicate]

Question

In my DB I have 2 Tables. One for user Login information and one for general information.

Im trying to write a query that will select the column "firstname" from rows where the FK "users_id" is the same as the logged in users ID.

Before doing anything in PHP Im running the query in my database, so the logged in users ID, which would normally be a variable, is replaced with the id of my testuser.

This is my query:

SELECT b6vjp_user_info.firstname 
          FROM b6vjp_user_info 
          WHERE b6vjp_user_info.users_id LIKE 243
          INNER JOIN b6vjp_users ON b6vjp_user_info.users_id=b6vjp_users.id;

And here is my (censored for security reasons) Login Table named "b6vjp_users":

And here is my other table named "b6vjp_user_info":

The error is:

#1064 - Mistake in SQL-Syntax. 'INNER JOIN b6vjp_users ON b6vjp_user_info.users_id=b6vjp_users.id LIMIT 0, 25' on row 4

Now fyi I translated that, because my work environment is in german. But im sure you know what a Syntax-Error is.

Anyways I checked the JOIN Part of my query over and over again and looked up the JOIN tutorial on W3Schools. But there is no apparent mistake.

Does anybody see what I somehow fail to?

@jarlh Oh god such a simple mistake. Do you want to do the answer so I can mark it as solved? Need to wait 3 days when doing my own answers. Thanks! — w00ds98
– w00ds98, Commented Sep 6, 2018 at 11:00

jarlh · Accepted Answer · 2018-09-06 11:00:49Z

1

Put the WHERE clause after the (last) ON clause.

answered Sep 6, 2018 at 11:00

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

w00ds98 Over a year ago

I can accept the answer in 12 Minutes :) Thanks!

jarlh Over a year ago

You're welcome!

Gordon Linoff · Accepted Answer · 2018-09-06 11:02:54Z

0

I strongly recommend using table aliases. You also need to fix the order of your SQL clauses. So:

SELECT ui.firstname 
FROM b6vjp_user_info ui JOIN
     b6vjp_users u
     ON ui.users_id = u.id
WHERE ui.users_id = '243';

Or, more simply without the JOIN:

SELECT ui.firstname 
FROM b6vjp_user_info ui 
WHERE ui.users_id = '243';

Notes:

The operand to LIKE should be a string. So, make it a string! Implicit type conversion causes all sorts of problems.
If you are not using wildcards, I think = is more informative. If users_id is really a number, then just use = 243 rather than LIKE.
WHERE goes after the FROM clause. JOIN is an operator in the FROM clause.
The JOIN is not necessary. Unless you are fetching columns from the users table (or need it for filtering which is highly doubtful), don't bother with it.

answered Sep 6, 2018 at 11:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

w00ds98 Over a year ago

Wow thanks! This write up was very informative! Ill make sure to try integrating all of this into my coding :)

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-06 11:12:35Z

0

you have to put where after on clause

  SELECT b6vjp_user_info.firstname 
              FROM b6vjp_user_info                   
              INNER JOIN 
              b6vjp_users ON  
              b6vjp_user_info.users_id=b6vjp_users.id
              WHERE b6vjp_user_info.users_id =243 // i think it int field so no need to use like operator

edited Sep 6, 2018 at 11:12

answered Sep 6, 2018 at 11:01

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Collectives™ on Stack Overflow

Incorrect SQL Syntax when using JOIN [duplicate]

3 Answers 3

2 Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Linked

Related