Subquery to count value based upon variable in parent query

[doggybag]

I am trying to use a variable from a column in the first select to populate the against clause in the second query.

select m.menuitem, m.menuname,m.menutype, @a := m.menusearch, 
(select count(*) as total from nametable where match(name) against (@a)  )  as total from menu m

Just wondering if this is possible, and whether my code is anywhere near close?

[r937]

have you tried this...

SELECT m.menuitem
     , m.menuname
     , m.menutype
     , m.menusearch
     , ( SELECT COUNT(*) 
           FROM nametable 
          WHERE MATCH(name) AGAINST ( m.menusearch ) ) 
            AS total 
  FROM menu AS m

[doggybag]

yes i tried this one and it gave me an error, "incorrect arguments AGAINST"

Not sure why it wont work

thx for help

[doggybag]

please help