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?
mysql
edited Nov 22 '18 at 12:33
Madhur Bhaiya
19.6k62236
asked Nov 22 '18 at 12:32
Phillip JPhillip J
244
add a comment |
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?
mysql
edited Nov 22 '18 at 12:33
Madhur Bhaiya
19.6k62236
asked Nov 22 '18 at 12:32
Phillip JPhillip J
244
add a comment |
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?
mysql
edited Nov 22 '18 at 12:33
Madhur Bhaiya
19.6k62236
asked Nov 22 '18 at 12:32
Phillip JPhillip J
244
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?
mysql
mysql
edited Nov 22 '18 at 12:33
Madhur Bhaiya
19.6k62236
asked Nov 22 '18 at 12:32
Phillip JPhillip J
244
edited Nov 22 '18 at 12:33
Madhur Bhaiya
19.6k62236
asked Nov 22 '18 at 12:32
Phillip JPhillip J
244
edited Nov 22 '18 at 12:33
Madhur Bhaiya
19.6k62236
edited Nov 22 '18 at 12:33
Madhur Bhaiya
19.6k62236
edited Nov 22 '18 at 12:33
Madhur Bhaiya
19.6k62236
19.6k62236
asked Nov 22 '18 at 12:32
Phillip JPhillip J
244
asked Nov 22 '18 at 12:32
Phillip JPhillip J
244
asked Nov 22 '18 at 12:32
Phillip JPhillip J
244
244
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 22 '18 at 12:34
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Thank you! What do you mean by Full Text Searching?
– Phillip J
Nov 22 '18 at 12:39
@PhillipJ do check: dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
– Madhur Bhaiya
Nov 22 '18 at 12:39
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53431123%2fsearch-for-rows-that-contain-a-string-from-another-table-list%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 22 '18 at 12:34
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Thank you! What do you mean by Full Text Searching?
– Phillip J
Nov 22 '18 at 12:39
@PhillipJ do check: dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
– Madhur Bhaiya
Nov 22 '18 at 12:39
add a comment |
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.
answered Nov 22 '18 at 12:34
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Thank you! What do you mean by Full Text Searching?
– Phillip J
Nov 22 '18 at 12:39
@PhillipJ do check: dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
– Madhur Bhaiya
Nov 22 '18 at 12:39
add a comment |
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.
answered Nov 22 '18 at 12:34
Madhur BhaiyaMadhur Bhaiya
19.6k62236
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.
answered Nov 22 '18 at 12:34
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 22 '18 at 12:34
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 22 '18 at 12:34
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 22 '18 at 12:34
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
Thank you! What do you mean by Full Text Searching?
– Phillip J
Nov 22 '18 at 12:39
@PhillipJ do check: dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
– Madhur Bhaiya
Nov 22 '18 at 12:39
add a comment |
Thank you! What do you mean by Full Text Searching?
– Phillip J
Nov 22 '18 at 12:39
@PhillipJ do check: dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
– Madhur Bhaiya
Nov 22 '18 at 12:39
Thank you! What do you mean by Full Text Searching?
– Phillip J
Nov 22 '18 at 12:39
Thank you! What do you mean by Full Text Searching?
– Phillip J
Nov 22 '18 at 12:39
@PhillipJ do check: dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
– Madhur Bhaiya
Nov 22 '18 at 12:39
@PhillipJ do check: dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
– Madhur Bhaiya
Nov 22 '18 at 12:39
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53431123%2fsearch-for-rows-that-contain-a-string-from-another-table-list%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
