sql - MySQL Find Related Articles

Question

I'm trying to select a maximum of 10 related articles, where a related article is an article that has 3 or more of the same keywords as the other article.

My table structure is as follows:

articles[id, title, content, time]
tags[id, tag]
articles_tags[article_id, tag_id]

Can I select the related articles id and title all in one query?

Any help is greatly appreciated.

Answer 1

@updated to exclude the searched article itself!

Something along these lines

select *
from articles
inner join (
 select at2.article_id, COUNT(*) cnt
 from articles a
 inner join articles_tags at on at.article_id = a.id
 # find all matching tags to get the article ids
 inner join articles_tags at2 on at2.tag_id = at.tag_id
     and at2.article_id != at.article_id
 where a.id = 1234  # the base article to find matches for
 group by at2.article_id
 having count(*) >= 3  # at least 3 matching keywords
) matches on matches.article_id = articles.id
order by matches.cnt desc
limit 10;  # up to 10 matches required