CODEWITHSUNDEEP
mysqlselectconditional-statementswhere-clauseconcatenation
Alex K
Alex K

Reputation: 7247

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 ?

Upvotes: 117

Views: 312061

Answers (6)

Viraj Dhamal
Viraj Dhamal

Reputation: 5325

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.

Upvotes: 8

Bogdan
Bogdan

Reputation: 44586

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.

Upvotes: 7

mdma
mdma

Reputation: 57787

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"

Upvotes: 184

Chandu
Chandu

Reputation: 82963

Try this:

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

Upvotes: 34

RC.
RC.

Reputation: 28267

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.

Upvotes: 7

Jeff Swensen
Jeff Swensen

Reputation: 3573

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

Upvotes: 10

Related Questions