search for fullname on joined table

Question

I've written a query to search by fullname on join table but I get this error. any idea?

SELECT wrs.id, wrs.fname, wrs.sname, wrs.email1, wrs.applied, wrs.firstlogin, wrs.tel1,
       SUBSTR( wrs.lastlogin, -10 ) AS last_login, 
       CONCAT_WS(  ' ', wrs.fname, wrs.sname ) AS full_name, wqs.level, 
       wqs.subject, wqs.university, wqs.dissertation
FROM temp_users AS wrs
    LEFT JOIN writer_qualifications AS wqs ON wqs.writer_id = wrs.id
WHERE wrs.fname LIKE  '%micky%'
   OR wrs.sname LIKE  '%micky%'
   OR full_name LIKE  '%micky%'
   AND wrs.status =  '1'
GROUP BY wrs.id


MySQL said: Documentation

#1054 - Unknown column 'full_name' in 'where clause'

for `OR full_name LIKE '%micky%'` to be true, either of `wrs.fname LIKE '%micky%'` and `wrs.sname LIKE '%micky%` would already be true — Ejaz, May 06 '14 at 16:27
See this question: http://stackoverflow.com/questions/11182339/reference-alias-calculated-in-select-in-where-clause — WillardSolutions, May 06 '14 at 16:29
@IsaacRajaei edited comment for clarity. I mean to say that you might not need to test `full_name` — Ejaz, May 06 '14 at 16:30
is there any idea how to search for full name for this query? — Fury, May 06 '14 at 16:36

VMai · Accepted Answer · 2014-05-06T16:37:34.823

You can't use an alias name like full_name in the WHERE clause, see manual

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

so you've got to use the expression CONCAT_WS( ' ', wrs.fname, wrs.sname ) in the WHERE clause instead of the alias full_name:

SELECT wrs.id, wrs.fname, wrs.sname, wrs.email1, wrs.applied, wrs.firstlogin, wrs.tel1,
   SUBSTR( wrs.lastlogin, -10 ) AS last_login, 
   CONCAT_WS(  ' ', wrs.fname, wrs.sname ) AS full_name, wqs.level, 
   wqs.subject, wqs.university, wqs.dissertation
FROM temp_users AS wrs
    LEFT JOIN writer_qualifications AS wqs ON wqs.writer_id = wrs.id
WHERE wrs.fname LIKE  '%micky%'
    OR wrs.sname LIKE  '%micky%'
    OR CONCAT_WS(  ' ', wrs.fname, wrs.sname ) LIKE  '%micky%'
    AND wrs.status =  '1'
GROUP BY wrs.id

what do you suggest then? what is this?!! OR CONCAT_WS( ' ', wrs.fname, wrs.sname ) LIKE '%micky%' — Fury, May 06 '14 at 16:34
Yes, the same construct that you use for your `full_name` instead of the alias name `full_name`. — VMai, May 06 '14 at 16:36

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

You can't use an alias of an aggregate ('full_name') in a WHERE clause because the server typically processes the WHERE clause before it does the aggregate work. This question/answer has great options and explanations for you: Reference alias (calculated in SELECT) in WHERE clause

edited May 23 '17 at 12:20

Community

1
1

answered May 06 '14 at 16:33

WillardSolutions

2,316
4
28
38

search for fullname on joined table

2 Answers2