Subquery field in Like

Question

I have the following query:

SELECT
(...),
(SELECT us.short FROM url_short us WHERE us.urlId = u.id
  ORDER BY us.id DESC LIMIT 1) as short, 
(...),
(SELECT f.name FROM `group` f LEFT JOIN group_url fu ON f.id = fu.group_id WHERE
  fu.url_id = u.id ORDER BY f.id DESC LIMIT 1) as f_name
FROM (...)
WHERE (...) AND
(u.url LIKE '%ops%'
OR short LIKE '%ops%'
OR u.url_name LIKE '%ops%'
OR f_name LIKE '%ops%')
(...)

However, when I try to use LIKE in short and f_group MySQL say me that:

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

I already search a lot and found nothing

score 2 · Answer 1 · answered Aug 02 '13 at 15:50

your where clause does not see the short alias as it is only introduced through the projection step taking place after the filtering of the result set (which is realized by the outermost WHERE clause).

try

SELECT *
  FROM (
          SELECT
          (...),
          (SELECT us.short FROM url_short us WHERE us.urlId = u.id
            ORDER BY us.id DESC LIMIT 1) as short, 
          (...),
          (SELECT f.name FROM `group` f LEFT JOIN group_url fu ON f.id = fu.group_id WHERE
            fu.url_id = u.id ORDER BY f.id DESC LIMIT 1) as f_name
          FROM (...)
          WHERE (...)
       ) emb
   WHERE (emb.url LIKE '%ops%'
          OR emb.short LIKE '%ops%'
          OR emb.url_name LIKE '%ops%'
          OR emb.f_name LIKE '%ops%')
       ;

Great idea friend, however I'll stay with Having, that Iwo had suggested 'cause it's faster and I need it to be fast. Thank you anyway, it's a good idea. — Helike Long, Aug 02 '13 at 17:33

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

You use alias in your query, and this probably causes this error. Here's another question with similar problem.

edited May 23 '17 at 12:28

Community

1
1

answered Aug 02 '13 at 15:45

Iwo Kucharski

3,735
3
50
66

Subquery field in Like

2 Answers2