SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Oct 2006
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Subquery to count value based upon variable in parent query

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

    Code:
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    have you tried this...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2006
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes i tried this one and it gave me an error, "incorrect arguments AGAINST"

    Not sure why it wont work

    thx for help

  4. #4
    SitePoint Zealot
    Join Date
    Oct 2006
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    please help


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •