I have a table called bank
with three columns: uid
, nick
, balance
.
I am trying to create a query that will return the balance based on the nick, and I am getting an error Unknown column 'Alex' in 'where clause'
when I use this query:
SELECT b.balance FROM bank AS b WHERE b.nick=`Alex` LIMIT 1
Can anyone see what I am doing wrong here?
MySQL considers the value to be a column name. To fix the error above, simply add a quotation mark around the value. You can use both single quotes or double quotes as shown below: INSERT INTO users(username, display_name) VALUES ("jackolantern", 'Jack');
Unknown column 'gender' in 'field list' in SQL / DEFAULT Constraint.
The MySQL unknown column in field list error happens when you put a column name in your SQL script that can't be found by MySQL. The error above is because there's no student_name column in the students table.
A HAVING clause is like a WHERE clause, but applies only to groups as a whole (that is, to the rows in the result set representing groups), whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause.
backticks (`) are used for identifiers, like table names, column names, etc. Single quotes(') are used for string literals.
You want to do:
SELECT b.balance FROM bank AS b WHERE b.nick='Alex' LIMIT 1
Or, to be more explicit:
SELECT `b`.`balance` FROM `bank` AS b WHERE `b`.`nick`='Alex' LIMIT 1
When there is no chance of ambiguity, and when table/column names do not have special characters or spaces, then you can leave the ` off.
Here is some documentation that is dry and hard to read: http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
But here is a related question on dba.stackoverflow that is easier to read: https://dba.stackexchange.com/questions/23129/benefits-of-using-backtick-in-mysql-queries
And here is a very good page that I recommend everyone read: http://www.sitepoint.com/forums/showthread.php?408497-the-big-bad-thread-of-quot-MySQL-Best-Practices-and-Other-Useful-Information-quot
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With