Help on a Query, Please!

Forum Moderators: open

Message Too Old, No Replies

Help on a Query, Please!

Finding "related" records

FourDegreez

7:09 pm on Sep 13, 2006 (gmt 0)

Say I have two tables: article, keyword

Each article record may have zero or many keyword records. For example, Article 1 has keywords A, B, and C. Article 2 has keywords B, C, and D.

I'm trying to come up with a query that will find a list of "related" articles, ordered by how many keywords they have in common. In my example Articles 1 and 2 would have two keywords in common, so Article 2 would be ordered ahead (in the resultset) of articles that may have only one keyword in common with Article 1.

But I can't figure out how this can be done. Can someone help?

FalseDawn

11:06 pm on Sep 13, 2006 (gmt 0)

How are your tables structured? If your "keyword" table has one record for every keyword in every article, it might be possible to knock up a query using IN(a,b,c,d), where a,b,c,d are keywords of the article you are interested in, and some sort of COUNT.
If you are storing keywords as a comma separated list, this is not something that SQL is suited for and would be better accomplished in code.
Also, the fact that you want "related articles" suggests that you want some type of "super" query that will compare each article to every other article, rather than just finding the best related articles to a specified article?
This is going to be pretty tricky to do in SQL.