mysqlmatchmatch-against

match article title with tags listed in another table


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


Solution

  • 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