I think you can get those rows that have multiple colors, if you know the list of colors to check for... Maybe this can help...
SELECT itemID from theTable Where colorID in (1,2,3,4,...N) Group by itemID Having count(*) = N; This will only return the itemID if it appears in all of the colors listed, *unless* an item can be listed in a color More than once. In that case, it'll return false rows. Kevin -----Original Message----- From: gamin [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 3:33 PM To: danchik; Fred Whipple; [EMAIL PROTECTED] Subject: Re: again with SELECT > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
