> thats effectively an OR statement, isnt it? > > how would i select the itemsIDs that have all the requested colors? > > for example if data was: > id : colorID : itemID > ----------------------------- > 1 1 1 > 2 1 2 > 3 1 3 > 4 2 1 > 5 2 4 > > and i want the ones that are of color 1 AND 2 i should get back > itemID > 1 > > (since its the only one with both specified colors) >
Normally (this is what most ppl would think), for a given item you have one single unique color, but as i see this is not in your case. You should reorganize your tables. But if for some reason you cannot, here is a suggestion (just off my head). Assuming that colorIDs do not take very large values. To each colorID you assign a unique prime number (maintain this in a table). For each itemID now you have a unique colorPRODUCT (product of the prime numbers coressponding the various colorIDs for this itemID). To add a colorID to an itemID simply muliply the new prime (coressponding to the colorID being added) to the old colorPRODUCT. In the same way to remove a colorID devide colorPRODUCT by the corressponding prime. And to find the itemID which has a particular set of colors just check for the colorPRODUCT. I dont think deleting will be such a problem also, say you want to delete all items which have colorID = 2. Find the corresponding prime, lets say it is P then you simply need to check if mod(colorPRODUCT, P) = 0. Of course no indexes can be used directly. Well, not such a bright suggestion !! G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
