----- Original Message ----- From: SGr...@unimin.com To: Mojtaba Faridzad Cc: mys...@lists.mysql.com Sent: Monday, July 26, 2004 11:15 AM Subject: Re: how to deal with a string of categories

May I suggest a design change as a solution? I would suggest that you should create a user/category table to replace your "catstr" field:

CREATE TABLE user_category ( user_Id int not null, category_Code char(1) not null UNIQUE (user_Id, category_Code) )

There will be one record in the user_category table for each category that a user belongs to.

To see a list of all of your users and to which cateogories each user belongs:

SELECT u.user_Name, category_Name FROM user u INNER JOIN user_category uc ON uc.user_ID = u.user_id INNER JOIN category cat ON cat.category_Code = uc.category_Code

or if you wanted a comma-separated list of categories for each person:

SELECT u.user_Name, GROUP_CONCAT(category_Name) as categories FROM user u INNER JOIN user_category uc ON uc.user_ID = u.user_id INNER JOIN category cat ON cat.category_Code = uc.category_Code GROUP BY u.user_Name

NOTE: you will have to change the query examples I gave you to match your actual table and field names!!!!

This does not limit you to having only 10 (or 20 or 30) categories for each person. It also means that creating new categories will not require a change in your database design (changing the size of a column) but only adding or deleting records.

Yours,

Shawn Green Database Administrator Unimin Corporation - Spruce Pine

"Mojtaba Faridzad" <mfar...@pylonelectronics.com> wrote on 07/26/2004 10:55:22 AM: