mysql - Where statement from concat/union script -

i have written below sql statement , results, when add name = statement doesn't recognise 'name'

select concat( `surname` , ' ', `firstname` ) name  prospects union  select concat( `last_name` , ' ', `first_name` ) name  customer order name; 

a couple of options.

add clause each select. it's not possible reference result of expression in select list assigned alias, in clause of same query. specify predicate in clause, need repeat expression:

 select concat(p.surname,' ',p.firstname) name     prospects p   concat(p.surname,' ',p.firstname)  = ?   union   select concat(c.last_name,' ',c.first_name)    customer c   concat(c.last_name,' ',c.first_name)  = ?   order 1 

mysql extends sql standard, , allows having clause reference non-aggregate expressions not in group

 select concat(p.surname,' ',p.firstname) name     prospects p  having name  = ?   union   select concat(c.last_name,' ',c.first_name) name     customer c  having name  = ?   order 1 

to reference alias name in clause, use inline view. note there can significant performance penalty, because predicate not pushed inline view. means mysql materialize of rows view query derived table, , outer query run against derived table. don't this, large tables:

select v.name   ( select concat(p.surname,' ',p.firstname) name             prospects p           union          select concat(c.last_name,' ',c.first_name)            customer c        ) v  v.name  = ?   order 1