tags:

views:

89

answers:

4

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

A: 

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;
assaqqaf
@assaqqaf: I dont think its correct. I want to be able to find itemIDs with the same catIDs based on a specified itemID. Based on all the categories of the item, not just in one.
Jonathan
+2  A: 

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 = ?
Bill Karwin
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.. Obviously it need to be done with all the items not only 3.. This way I can recommend visitors of similar products...
Jonathan
@Jonathan: please edit this "little" amendment into your original question.
VolkerK
+1  A: 

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;
Nick
Number of categories won't do. The categories have to be the same.
siride
Of course it will only return items with the same categories :) I was talking about the number of categories only in reference to the subsequent ordering.
Nick
@Nick: Looks like its doing what I want! Thanks.@siride: You only say like 'Number of categories won't do' and 'If the result can't be drawn as a table, then there's no SQL'. Just post a correct answer if you have it. It will help more...
Jonathan
@Nick: What if I need to order the list by categories matched and also by highest vote taken from another table? The table 'votes' contain two columns: 'itemID' and 'total_value', 'total_value' being the final rate. Can you post the updated code separately? Thanks!
Jonathan
A: 

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
Thomas
Well, the second code you posted is wrong. First of all is C and not C1. And second and most important I restarted the server after running the code because apache something went wrong and everything just went slow. Chrome stopped working and Apache went Off.
Jonathan
@Jonathan - I can't speak to your setup however, I have corrected the typos. I'm assuming that the categories table has an index on both itemId and catId? Group_Concat can be an expensive operation but then you also did not mention how many rows are involved nor even whether you actually wanted a concatenated list. We are all guessing as to what you actually seek.
Thomas
Well I think that if I need to compare all the tables items is something like 6000+ items.. What do you think is the best way to do it?
Jonathan
@Jonathan - Firstly, if you filter for a single itemID, does the query return the correct result? If it does, then we are talking about ways of making it more efficient.
Thomas