CODEWITHSUNDEEP
mysqlsql
Hritam
Hritam

Reputation: 51

Get a table row if it matches another table row string

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

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

tbl_paragraph

enter image description here

tbl_keyword

enter image description here

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

enter image description here

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.

Upvotes: 0

Views: 265

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

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.

Upvotes: 1

Related Questions