Get a table row if it matches another table row string

Question

I am trying to fetch a row from a table which matches rows from another table without stored procedure in single query. Is this possible? Let me explain graphically. Suppose I have two tables

tbl_paragraph
tbl_keywords

I am trying fetch all the paragraph rows whoch matches all the keyword rows.

tbl_paragraph

tbl_keyword

Here when I will try to fetch paragraphs which matches Keyword tables keyword column. So the result will come like as follows

Though I have tried a sql

SELECT a.* 
FROM tbl_paragraph AS a 
INNER JOIN tbl_keywords b ON a.title LIKE '%b.keyword%'

But it's not working.

Possible duplicate of [MySQL like another field](https://stackoverflow.com/questions/4886295/mysql-like-another-field) — Madhur Bhaiya, Sep 11 '19 at 07:00

score 1 · Accepted Answer · answered Sep 11 '19 at 07:03

Rather than using LIKE here, I would actually suggest using REGEXP, which by default is case insensitive:

SELECT a.*
FROM tbl_paragraph a
WHERE EXISTS (SELECT 1 FROM tbl_keywords b
              WHERE a.title REGEXP CONCAT('[[:<:]]', b.keyword, '[[:>:]]'));

The other advantage of using REGEXP over LIKE, other than the case sensitive problem, is that the former lets us use word boundaries. As I have written the above query, it would match a keyword we only as a standalone word. That is, it would not match weed against we. This is equivalent to searching for \bwe\b in regular regex lingo.

Get a table row if it matches another table row string

1 Answers1