[mysql] MySQL select with CONCAT condition

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 ?

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.

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.

SELECT needefield, CONCAT(firstname, ' ',lastname) as firstlast 
FROM users 
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"

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.

Try this:

SELECT * 
  FROM  (
        SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast 
        FROM users 
    ) a
WHERE firstlast = "Bob Michael Jones"