Unknown column in WHERE clause when using subquery

Question

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.

score 1 · Accepted Answer · answered Aug 17 '14 at 00:38

1

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%'

answered Aug 17 '14 at 00:38

Brian DeMilia

13,103
1
23
33

1

It should be faster; there is no reason for you to have a subquery in the SELECT list in this situation. – Brian DeMilia Aug 17 '14 at 12:50

score 1 · Answer 2 · answered Aug 17 '14 at 00:44

1

Try this....

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

answered Aug 17 '14 at 00:44

M.Ali

67,945
13
101
127

This looks like triple subquery! – Praveen Kumar Purushothaman Aug 17 '14 at 09:52

score 0 · Answer 3 · edited Jun 21 '16 at 18:37

0

You can use a variable:

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

edited Jun 21 '16 at 18:37

Dipen Shah

1,911
10
29
45

answered Jun 21 '16 at 18:15

Ruggero

1
1

Unknown column in WHERE clause when using subquery

3 Answers3