MySQL select with CONCAT condition

mysql select conditional-statements where concat

Alex K · Apr 20, 2011 · Viewed 296.2k times · Source

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 ?

Answer

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"

MySQL select with CONCAT condition

Answer

Related questions