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" <[EMAIL PROTECTED]> wrote on 07/26/2004
10:55:22 AM:
> Hi,
>
> I'd like to know how you guys write SQL command for this problem. there
are
> some categories which I give them "A", "B", ... and I have a string
field
> (catstr) with 10 characters in "mytable" to keep selected categories in
a
> record. when user selects "C", "G", "K", I keep "CGK" in "catstr" field.
> categories has been defined in "cattable". now I want to write a query
to
> retreive these catergories. query will have 10 columns ("catstr" is 10
> characters) with description of categories. it means user doesn't see
"C",
> or "G".
>
> I tried to open 10 times "cattable" with different alias and make the
query
> but it looks like MySQL doesn't like it and doesn't let me open the same
> table more than once (even with different alias). I can create 10
temporary
> tables and solve this problem and it doesn't look good. or I can create
my
> query with 10 CASE commands with I create them base on "cattable". how
you
> guys solve this problem? maybe there is a better solution which I don't
know
> and very neat can solve this problem.
>
> thanks,
> Mojtaba
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
