Praveen Kumar Praveen Kumar - 2 years ago 226
MySQL Question

Unknown column in WHERE clause when using subquery

I have a query like the below:

SELECT `name` FROM `users` WHERE `users`.`id`=``.``
) AS `fullName` FROM `listings` WHERE `fullName` LIKE '%praveen%';

But when I execute the above query, I am getting this error:

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

I know that this bug has been documented. I am not sure what's the workaround for this other than doing something like:

SELECT `name` FROM `users` WHERE `users`.`id`=`listings`.`user`
) FROM `listings` WHERE (SELECT `name` FROM `users` WHERE users`.`id`=`listings`.`user`) LIKE '%praveen%';

Is there any other way I can do this other than creating a view, or using a query like the above? I have tried referring other questions:

I couldn't find a better solution. What's the best can I do in this case? Thanks in advance.

Answer Source

I think this is the correct syntax of what you are after:

select l.*, as fullname
  from listings l
  join users u
    on l.user =
 where like '%praveen%'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download