Your method is wrong because if you are looking for "5", then "56" will match and also everything that has "5" in it.
Unless you include the spaces and look for : '% 5 %' but then you need to add a space at the beginning and at the end of your attributes field. That method is way too cumbersome and also creates some optimization issues, since you need to have that attribute field with a fixed varchar type, whether the product has attributes or not, no matter what the size of the attributes strings will be, and you need to make the field large enough (50 characters? 100?) to avoid running out of "space". Eventually you will end up with a big fat products table and your app will crawl. You need to use three tables: one for your product, one for the attributes and one link table. ID (integer) fields are used to link the three tables. You can fine-tune your tables by selecting the proper integer type you need (smallint, mediumint or int) to optimize your database properly. Then you need to do your search on the link table, inner joined to the products table on the product id or not. When you pull queries on the link table only (for instance to count how many with such attribute), the query will fly. The link table also allows one to many relationships. Believe me, that's the way to do it. -----Original Message----- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 07, 2006 10:45 PM To: MySql Subject: Best way to design one to many queries Been wondering this for a while now, I can explain how I do it, but I am sure there are better ways. I have products and attributes, for example: Product A nice sweet fast funny Attributes are arbitrary, attributes are in a separate table. Yes, I could add fields for the attributes to the products table, but that does not allow the client the flexibility to add new attributes on the fly. Generally, in the products table I have a text filed where I store the id of the attribute, space separated, for example: attributes: 1 5 23 56 3 6 The trouble is, when I want to find all products where the attribute id is 5 and 23 and 3, I have to build it up like so: Select prod_name from products where attribute_id like ' 5 ' AND etc etc etc which also leads to issues with leading and trailing spaces on the first and last attribute id's Suggestions? -- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 <http://www.newgeo.com> Novato, CA U.S.A. -- 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]
