CODEWITHSUNDEEP
mysql
Phillip J
Phillip J

Reputation: 57

Search for rows that contain a string from another table list

so I have 2 tables. chat_table, bad_words_table. Now I need a query that searches all chats that have bad words.

chat_table(id, user, chat) - contains all chats

bad_words_table(id, word) - contains a list of offensive words.

Now I need some sort of query that takes all chats from chat_table WHERE the chat CONTAINS any of the words FROM bad_words_table;

What would be the best way to do that?

Upvotes: 1

Views: 991

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can JOIN between the two tables such that chat LIKE %bad_word%. A chat may contain multiple bad words; we can get unique chats only using DISTINCT keyword.

SELECT DISTINCT c.*
FROM chat_table AS c
JOIN bad_words_table AS bw 
  ON c.chat LIKE CONCAT('%',bw.word,'%')

For larger tables, I would recommend to use Full Text Searching instead, as LIKE %..% will not be able to use any Indexing.

Upvotes: 2

Related Questions