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!