Query a list of names from one table that appear in a field in a different table

Question

I want to query a list of names from one table that appear in a field in a different table.

Example:

table1.title>Tiger Woods Cheats, Tiger Woods Crashes, Brad Pitt is Great, Madonna Adopts, Brad Pitt Makes a Movie

table2.names>Tiger Woods, Brad Pitt, Madonna

So those are the two tables and values. I would like to write a query that counts which names from table2.names appear most often in table1.title

Someone suggested using inner join, but I could not get it to work... I appreciate the help!! Thanks.

Answer 1

You do want to use a join, however your join condition will be a pattern match, not an equality. This assumes you want to look for exact name matches (i.e. the exact value from table2.names being included in the table1.title column somewhere/anywhere within the string and not partial matches):

select   t2.names, count(*)
from     table2 t2
join     table1 t1
on       t1.title like('%' + t2.names + '%')
group by t2.names
order by count(*) desc;

Answer 2

Use:

  SELECT a.names,
         COUNT(b.titles) AS num
    FROM TABLE_2 a
    JOIN TABLE_1 b ON INSTR(b.title, a.names) > 0
GROUP BY a.names
ORDER BY num DESC

See the documentation about INSTR() - checking for a value greater than 0 means the name occurred in the title, otherwise it would be zero.

The AS num is a column alias, which you can reference in the ORDER BY to sort either in ASCending or DESCending order.