I need a query in mysql that can list out matched tag_names in one table with article_title column in another table
tags table ------------ tag_id tag_name -------------- 1 travel 2 tickets 3 business 4 america
article table ------------- article_id article_title --------- -------------- 1 travel tips to america 2 cheap tickets for favorite destinations 3 prices for business class tickets to america
expected output -------------- article_id tag_id tag_name ---------- ------- ---------- 1 1 travel 1 4 america 2 2 tickets 3 3 business 3 2 tickets 3 4 america
The query should be as follows:
SELECT a.article_id, t.tag_id, t.tag_name
FROM article a
JOIN tags t
ON a.article_title LIKE CONCAT('%', t.tag_name, '%')
ORDER BY a.article_id;
However, if you want to tokenize the tags with space, you should replace line 4 of the query with
ON a.article_title LIKE CONCAT('% ', t.tag_name, ' %')
This will not consider the tag america in the titles like
The american dream is a national ethos of the United States