SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2006
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to count how many times words from one table match a column in another table

    I am not even sure if a query like this is possible without some scripting around it.

    I have a table of keywords (ie: bunny, hamster, chicken, clock, wall, etc.) and I want to count how many times those keywords are found in the title column of another table in my database.

    The other table has a column called 'title' and it is filled with rows of information like:

    The black bunny and yellow chicken turned back the clock.
    We have five clocks on our wall.
    Our house has two hamsters and one chicken stuck in the wall.
    Every bunny has its picture on someones clock.

    I want the query to count up how many times the keywords appear in all of the rows of titles.

    Any ideas?

  2. #2
    SitePoint Member
    Join Date
    Jul 2006
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    SELECT COUNT( w.word ) AS total, w.word
    FROM data d
    RIGHT JOIN words w ON ( w.word LIKE '%d.title%' )
    GROUP BY w.word
    ORDER BY total DESC

    I attempted this, but it just returns 1 for the count for each of the words.

  3. #3
    SitePoint Evangelist bradical1379's Avatar
    Join Date
    Feb 2007
    Posts
    443
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @zeropsi Interesting.

    I have been trying to accomplish something very similiar with zero luck so far.

    I don't necessarily have a solution, however, I think the RIGHT JOIN clause is incorrect.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    assuming this is for mysql (if not, the concatenation function is different)...
    Code:
    SELECT COUNT(*) AS total
         , w.word
      FROM words w
    INNER
      JOIN data d 
        ON d.title LIKE CONCAT('%',w.word,'%') 
    GROUP 
        BY w.word
    ORDER 
        BY total DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •