SELECT id, firstname, lastname, is_online, adate, photo, rdate, ( select count(*) from instance2_interests_users b where b.uid = a.id and b.`is_deleted` = 0 ) as fg FROM `instance24_users` a WHERE fg > 0 and a.`is_deleted` = 0 ORDER BY `rdate` asc

Why does this error occur?

 Unknown column 'fg' in 'where clause'

    1 answer 1

    fg is not defined at this level.

    You need to use the HAVING operator.

    I would rewrite the query like this:

     SELECT id, firstname, lastname, is_online, adate, photo, rdate, count(b.id) as fg FROM `instance24_users` as a LEFT JOIN instance2_interests_users as b ON (b.uid = a.id and b.`is_deleted` = 0) WHERE a.`is_deleted` = 0 GROUP BY id, firstname, lastname, is_online, adate, photo, rdate HAVING count(b.id) > 0 ORDER BY `rdate` asc

    Sorry, there is nothing on hand to check the validity of the request. There may be typos.

    • However, it works after some changes. - Oleg