I have a table with an itemID and a categoryID columns. Both columns are primary keys because each item can have more than 1 category:
itemID | catID
-----------------
1 | 2
1 | 3
1 | 4
2 | 2
2 | 3
2 | 4
I want to select items with the same categories (based on all the item categories, not just one) so I need to kind of JOIN the same table.
I want to be able to find itemIDs with the same catIDs based on a specified itemID.
Note: For example item 1 have categories 2,3,4,5,6 and item 2 have categories 2,3,4,5,6 and item 3 have categories 3,5,6 then if i compare item 1 to item 2 and 3 i need to get item 2 first and then item 3 because item 2 have more categories matches than item 3.. Obviously it need to be done with all the items not only 3.. This way I can recommend visitors of similar products...