mysql - Search number of occurrences in a table based on data from a second table

Question

DB Fiddle example: https://www.db-fiddle.com/f/eGee7uKqPRLUxMeMjwGoaH/0

I have two tables, table A contains strings with multiple words and table B contains words that I'm trying to find.

Table A looks like this:

PostContents                               PostId
doggo walks his cat and moose              1111
moose just ate the dog but not my ape      1234
buffalo runs faster than a rhino           4444

Table B has the following data:

SearchString
dog
giraffe
moose

I want to count all occurrences for the strings that are in table B. The word "dog" appears once ("doggo" does not count) and the word moose appears two times, giraffe does not appear at all. The desired output would be a sum of total appearances for each of those words - dog is found once, moose is found twice, so the result would simply be the number 3.

I've tried joining with like and but I'm not satisfied with the results. Thank you for helping me out!

question from:https://stackoverflow.com/questions/65830536/search-number-of-occurrences-in-a-table-based-on-data-from-a-second-table

Answer 1

You need left join with like as follows:

Select b.searchstring, count(a.postid) as ocrnc
 From tableb b
 Left Join tablea a on concat(' ', a.postcontent, ' ') like concat('% ', b.searchstring, ' %')
Group by b.searchstring