http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html
Is where you will find the GROUP_CONCAT function in the manual.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Mojtaba Faridzad" <[EMAIL PROTECTED]> wrote on 07/26/2004
11:34:30 AM:
> 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]
> >
