MySQL select with CONCAT condition
MysqlSelectConditional StatementsWhereConcatMysql Problem Overview
I'm trying to compile this in my mind.. i have a table with firstname and lastname fields and i have a string like "Bob Jones" or "Bob Michael Jones" and several others.
the thing is, i have for example Bob in firstname, and Michael Jones in lastname
so i'm trying to
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
WHERE firstlast = "Bob Michael Jones"
but it says unknown column "firstlast".. can anyone help please ?
Mysql Solutions
Solution 1 - Mysql
The aliases you give are for the output of the query - they are not available within the query itself.
You can either repeat the expression:
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"
or wrap the query
SELECT * FROM (
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users) base
WHERE firstLast = "Bob Michael Jones"
Solution 2 - Mysql
Try this:
SELECT *
FROM (
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
) a
WHERE firstlast = "Bob Michael Jones"
Solution 3 - Mysql
SELECT needefield, CONCAT(firstname, ' ',lastname) as firstlast
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"
Solution 4 - Mysql
Use CONCAT_WS().
SELECT CONCAT_WS(' ',firstname,lastname) as firstlast FROM users
WHERE firstlast = "Bob Michael Jones";
The first argument is the separator for the rest of the arguments.
Solution 5 - Mysql
Try:
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"
Your alias firstlast is not available in the where clause of the query unless you do the query as a sub-select.
Solution 6 - Mysql
There is an alternative to repeating the CONCAT
expression or using subqueries. You can make use of the HAVING
clause, which recognizes column aliases.
SELECT
neededfield, CONCAT(firstname, ' ', lastname) AS firstlast
FROM
users
HAVING firstlast = "Bob Michael Jones"
Here is a working SQL Fiddle.