Search code examples
mysqlsqljoingroup-bymysql-error-1054

unknown column xxx in where clause using joins, group by and fulltext

What is wrong with my query?

SELECT ...
       MATCH(results.keywords) AGAINST('".DB::e($_POST['s'])."' IN BOOLEAN MODE) AS aaa,
       SUM( MATCH(tags.keywords) AGAINST('".DB::e($_POST['s'])."' IN BOOLEAN MODE) ) AS bbb
  FROM table1
  JOIN table2 ... JOIN table3 ...
 WHERE (aaa > 0   here the alias doesn't work!
        OR bbb > 0   here the alias doesn't work! )
GROUP BY table1.id
ORDER BY aaa DESC,  but here the alias works!
         bbb DESC  but here the alias works!

If I remove the WHERE clause works perfectly... so what is wrong with my aliases?

Solution

  • You need to use HAVING (here and here and somewhat less helpfull: here); the WHERE clause gets evaluated before the aliases even exist ;)