zeropsi — 2012-05-30T16:40:17-04:00 — #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.
zeropsi — 2012-05-30T18:06:27-04:00 — #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.
bradical1379 — 2012-05-30T20:34:45-04:00 — #3
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.
r937 — 2012-05-30T21:24:45-04:00 — #4
assuming this is for mysql (if not, the concatenation function is different)...
SELECT COUNT(*) AS total
FROM words w
JOIN data d
ON d.title LIKE CONCAT('%',w.word,'%')
BY total DESC