CODEWITHSUNDEEP
mysqlsqlsubquerywhere-clause
Praveen Kumar Purushothaman
Praveen Kumar Purushothaman

Reputation: 167192

Unknown column in WHERE clause when using subquery

I have a query like the below:

SELECT *, (
    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 *, (
    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.

Upvotes: 0

Views: 4110

Answers (3)

Ruggero
Ruggero

Reputation: 1

You can use a variable:

SELECT *, @var_fullName := (
    SELECT `name` 
    FROM `users` 
    WHERE `users`.`id`=``.``
) AS `fullName` 
FROM `listings` 
WHERE @var_fullName LIKE '%praveen%';

Upvotes: 0

M.Ali
M.Ali

Reputation: 69554

Try this....

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

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

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

select l.*, u.name as fullname
  from listings l
  join users u
    on l.user = u.id
 where u.name like '%praveen%'

Upvotes: 1

Related Questions