SELECT JOIN in same table ON multiple rows...

Question

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...

Answer 1

You have many to many relation, so the query will look like:

SELECT item.name 
  FROM item AS b 
   JOIN itemcategories AS ab ON b.ID = ab.itemID 
 where ab.catID =2;

Answer 2

So you want to choose one itemID, then match it to all other itemID's that share one or more catID's?

SELECT DISTINCT c2.itemID
FROM categories c1
JOIN categories c2 ON c1.catID = c2.catID
WHERE c1.itemID = ?

Answer 3

Building on Bill's initial query, this ought to order it in descending order by the number of categories matched (since the join should return one row per match). I also excluded the item being queried on from the result.

SELECT c2.itemID
FROM categories c1
JOIN categories c2 ON c1.catID = c2.catID
WHERE c1.itemID = :id
AND c2.itemID <> :id
GROUP BY c2.itemID
ORDER BY count(c2.itemID) DESC;

Answer 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.

Without mentioning anything else about the output, you can do something like:

Select C.itemid
From categories As C
Where Exists    (
                Select 1
                From categories As C2
                Where C2.catID = C.catID
                    And C2.itemID <> C.itemID
                )
   And C.itemID = ?
Group By C.itemid

(From comments)

Something like that, but ORDERED by items that have the most categories matches. 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

This adds a different complexion to the question and is why you should include expected output in your original post. Taking what you have written literally:

Select C.itemid, Group_Concat(C.catID Order By C.catID ) As Categories
    , Count(*) As MatchCount
From categories As C
    Join categories As C2
        On C2.itemID <> C.itemID
            And C2.catID = C.catID
Group By C.itemID
Order By Count(*) Desc