Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  most similar items in many-to-many system?

Author  Topic  mattd Starting Member 11 Posts Posted - 2001-05-31 : 18:12:09 Hi all, Suppose I have a table of things... tblThings --- ThingID ThingName ...and a table of categories... tblCategories --- CategoryID CategoryName ...and then a table to store the categories each thing belongs to... tblThingCategories --- ThingID CategoryID My question is, given a ThingID, what is the best/fastest way of of finding the top n other ThingIDs which have the most categories in common with the first thing? The best way I've thought of so far is to loop through each of the categories of the first thing, put any other things with that category into a temp table, and then do a "group by" on the temp table to get a count, ordering by the count and getting the top however many I want. That would work, but I'm wondering if anyone can think of any shortcuts...? Thanks in advance, matt