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

Databases
1

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 
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

@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

assuming this is for mysql (if not, the concatenation function is different)…

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