Clash Royale CLAN TAG

How to find the best match between 2 keywords lists

I'm making an ad network for my specific needs:

When a viewer goes into the User's article, what I need to find is the ad with the highest amount of matching keywords to that specific article (or any ad if no matching keywords)

User keyword table example:

ArticleID | keyword
1 | funny
1 | memes
1 | lol
2 | boring


Advertiser keyword table example

AdId | keyword
1 | funny
1 | lol
2 | funny
2 | food
3 | kids


So if a viewer will go into the article with id=1, the resulting ad will be
the ad with id=1, because it has matched both "funny" and "lol", a more detailed result:

Result:
AdId | count
1 | 2
2 | 1
3 | 0


How can I build a query to do that?

1 Answer
1

You could join on the keywords and count the results:

SELECT AdId, COUNT(*)
FROM ad_keywords a
JOIN user_keywords u ON a.keyword = u.keyword
WHERE article_id = 1
GROUP BY AdId
ORDER BY 2 DESC
LIMIT 1


limit

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.