CODEWITHSUNDEEP
mysqlsqlselectwhere-clausealias
Marc
Marc

Reputation: 440

Using WHERE in aliased concatenated fields gives 'unknown column' error

I have the following query:

SELECT a.*, CONCAT_WS(' ', `c`.`firstname`, `c`.`lastname`) AS `customer_fullname`
FROM `tickets` a        
LEFT JOIN `customers` `c` ON (`a`.`id_customer` = `c`.`id_customer`)
WHERE a.`id_raffle` = 1  AND `customer_fullname` LIKE '%John%'
ORDER BY a.`id_ticket` ASC LIMIT 0,50

And I get the error:

Unknown column 'customer_fullname' in 'where clause'

Any advice? Please note that I can't refactor the query because it's generated by a class I'm extending.

Upvotes: 0

Views: 133

Answers (2)

Nikitha
Nikitha

Reputation: 373

This is related to the order of execution of a SQL query.

You can't put alias name in WHERE clause because WHERE clause is executed prior to the alias naming. HAVING worked fine because HAVING is executed after alias naming is done.

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

You can't use column alias name in WHERE clause.

Try this: 

SELECT a.*, CONCAT_WS(' ', `c`.`firstname`, `c`.`lastname`) AS `customer_fullname`
FROM `tickets` a        
LEFT JOIN `customers` `c` ON (`a`.`id_customer` = `c`.`id_customer`)
WHERE a.`id_raffle` = 1  AND CONCAT_WS(' ', `c`.`firstname`, `c`.`lastname`)  LIKE '%John%'
ORDER BY a.`id_ticket` ASC 
LIMIT 0,50

OR

SELECT a.*, CONCAT_WS(' ', `c`.`firstname`, `c`.`lastname`) AS `customer_fullname`
FROM `tickets` a        
LEFT JOIN `customers` `c` ON (`a`.`id_customer` = `c`.`id_customer`)
WHERE a.`id_raffle` = 1  AND (`c`.`firstname` LIKE '%John%' OR `c`.`lastname` LIKE '%John%') 
ORDER BY a.`id_ticket` ASC 
LIMIT 0,50

Upvotes: 2

Related Questions