This is a complicated situation (for me) that I'm hopeful someone on here can help me with. I've done plenty of searching for a solution and have not been able to locate one. This is essentially my situation... (I've trimmed it down because if someone can help me to create this query I can take it from there.)

TABLE articles (article_id, article_title)

TABLE articles_tags (row_id, article_id, tag_id)

TABLE article_categories (row_id, article_id, category_id)

All of the tables have article_id in common. I know what all of the tag_id and category_id rows are. What I want to do is return a list of all the articles that article_tags and article_categories MAY have in common, ordered by the number of common entries.

For example:

article1 - tags: tag1, tag2, tag3 - categories: cat1, cat2

article2 - tags: tag2 - categories: cat1, cat2

article3 - tags: tag1, tag3 - categories: cat1

So if my article had "tag1" and "cat1 and cat2" it should return the articles in this order:

article1 (tag1, cat1 and cat2 in common)

article3 (tag1, cat1 in common)

article2 (cat1 in common)

Any help would genuinely be appreciated! Thank you!