Mysql match against unknown column

Question

I have a problem with this mysql query:

select * from tb1 as a 
 where (select count(*) from tb2 as b 
         where Match(b.column1) against(`a.id`) )  = '1'

Mysql has this error:

1054 - Unknown column 'a.id' in 'where clause'

So I want to get all entries in tb1 where the number of entries in tb2 where a.id is inside the 'column1' column is 1.

I hope you understand my purpose otherwise feel free to ask.

Answer 1

The table name should be outside the quotes, "a.id" is now handled as the column name (which off course doesn't exists), instead of a table.column pair:

against(a.`id`) 

Answer 2

I'm nearly sure that the problem is in data range. You use a subquery and in this subquery there is no info about 'a'. You can use data from subquery in main query but not inversely. Try something like that (I can't test it so I can't guarantee query works):

SELECT a.*, b.count(*) FROM tb1 AS a 
LEFT JOIN tb2 AS b ON Match(b.column1) against(a.id)
HAVING b.count(*) = 1