Thank Martijn, Brent, and Shawn so much! I did not know about GROUP_CONCAT() funtion.
I checked MySQL document page but just under "string function" in User Comments
section there was an example of using it. Shawn, where can I find the syntax of this
command? it looks like we can pass some parameters to it too (like SEPARATOR)
thanks,
Mojtaba
----- Original Message -----
From: [EMAIL PROTECTED]
To: Mojtaba Faridzad
Cc: [EMAIL PROTECTED]
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" <[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]
>
