Rather than using primes, you could use the SET datatype http://www.mysql.com/doc/en/SET.html
This allows for a color to be zero or more values at the same time. SELECT * FROM tbl_name WHERE color = '1,2,3,n' Regards, Mike Hillyer www.vbmysql.com -----Original Message----- From: Kevin Fries [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 4:51 PM To: 'gamin'; 'danchik'; 'Fred Whipple'; [EMAIL PROTECTED] Subject: RE: again with SELECT 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
