MySQL: Intersect rows that match a certain number of characteristics

Question

This is a little tough to explain, but here goes. I have a database of books, and in it two tables.

One for unique book keywords, i.e. just attributing an ID to each keyword:

id    |    keyword
______|____________
k1    |    drama
k2    |    thriller
k3    |    biography

And one for matching book IDs to keyword IDs (i.e., all the keywords for each book):

book_id   |   keyword_id
__________|_____________
b1        |   k3
b2        |   k1
b2        |   k2

So basically, book "b1" has keyword "biography," and book "b2" has keywords "thriller" and "biography".

My question is: if I have a certain book ID (say "b2"), is there a way for me to find other books in the database that have at least 2 (or any other number) matching keywords with "b2"? So in this example, other books which also have "k1" and "k2" as keywords? This would be a way to find "related books."

I would welcome any help. Please let me know if you want any clarification as to what I'm asking!

I think there may be a way to generate a "match score" for each movie, and then to sort movies in descending order by match score (i.e. highest match score is "most related"). Am I on the right path? — vinsanity555, Oct 06 '14 at 01:19

score 2 · Accepted Answer · answered Oct 06 '14 at 01:22

Yes. In fact, you only need the second table.

select bk.book_id, count(*)
from BookKeywords bk join
     BookKeywords bkb2
     on bkb2.book_id = 'b2' and
        bk.keyword_id = bkb2.keyword_id
group by bk.book_id
having count(*) >= 2;

If you want the list of matching keywords, you can add group_concat(bk.keyword_id).

MySQL: Intersect rows that match a certain number of characteristics

1 Answers1