SELECT JOIN in same table ON multiple rows

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

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 = ?

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;

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;

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