Error in MySQL Query in C#?

Question

I'm receiving an unexpected error on my MySQL query, can anyone help me discover why?

Error message:

MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON users.id = user_auth_tickets.user_id WHERE user_auth_tickets.auth_ticket = 'N' at line 1

Query:

dbConnection.SetQuery("SELECT users.id,users.username,users.rank,users.motto,users.look,users.gender,users.last_online,users.credits,users.activity_points,users.home_room,users.block_newfriends,users.hide_online,users.hide_inroom,users.vip,users.account_created,users.vip_points,users.machine_id,users.volume,users.chat_preference,users.focus_preference,users.pets_muted,users.bots_muted,users.advertising_report_blocked,users.last_change,users.gotw_points,users.ignore_invites,users.time_muted,users.allow_gifts,users.friend_bar_state,users.disable_forced_effects,users.allow_mimic,users.rank_vip " +
  "FROM users" +
  "JOIN user_auth_tickets " +
  "ON users.id = user_auth_tickets.user_id " +
  "WHERE user_auth_tickets.auth_ticket = @sso AND last_ip = @lastIp " +
  "LIMIT 1"
);

Answer 1

You have two WHERE clauses in your query. It just appears you may have had a shorter initial query, where you did place the WHERE clause properly, but then later decided to JOIN additional tables and assumed additional conditions. At that time you would have needed to restructure the query, as per the syntax hierarchy in the reference below, the WHERE comes after your table references.

Per the MySQL 5.6 Reference Manual - SELECT Syntax

The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.

Remove the first WHERE, and append the last_ip = @lastIp to the lower WHERE.

For example:

dbConnection.SetQuery("SELECT users.id,users.username,users.rank,users.motto,users.look,users.gender,users.last_online,users.credits,users.activity_points,users.home_room,users.block_newfriends,users.hide_online,users.hide_inroom,users.vip,users.account_created,users.vip_points,users.machine_id,users.volume,users.chat_preference,users.focus_preference,users.pets_muted,users.bots_muted,users.advertising_report_blocked,users.last_change,users.gotw_points,users.ignore_invites,users.time_muted,users.allow_gifts,users.friend_bar_state,users.disable_forced_effects,users.allow_mimic,users.rank_vip " +
"FROM users " +
"JOIN user_auth_ticket ON users.id = user_auth_ticket.user_id " +
"WHERE user_auth_ticket.auth_ticket = @sso AND last_ip = @lastIp " +
"LIMIT 1"
);