List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 26 2004 3:50pm
Subject:Re: how to deal with a string of categories
View as plain text  
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" <mfaridzad@stripped> 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: SGreen@stripped 
> To: Mojtaba Faridzad 
> Cc: mysql@stripped 
> 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" <mfaridzad@stripped> 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/mysql?unsub=1
> > 
Thread
Errcode: 27J S23 Jul
Re: Errcode: 27Aman Raheja23 Jul
Re: Errcode: 27J S24 Jul
RE: Errcode: 27J S24 Jul
  • Fulltext boolean search resultsleegold25 Jul
    • Re: Fulltext boolean search resultsMichael Stassen25 Jul
      • Re: Fulltext boolean search resultsleegold25 Jul
        • Re: Fulltext boolean search resultsMichael Stassen25 Jul
RE: Errcode: 27J S26 Jul
  • how to deal with a string of categoriesMojtaba Faridzad26 Jul
    • Re: how to deal with a string of categoriesBrent Baisley26 Jul
    • Re: how to deal with a string of categoriesSGreen26 Jul
      • Re: how to deal with a string of categoriesMojtaba Faridzad26 Jul
        • Re: how to deal with a string of categoriesSGreen26 Jul
  • Re: how to deal with a string of categoriesMartijn Tonies26 Jul
  • how to set timeout processMojtaba Faridzad27 Jul
    • /etc/my.cnfWolfgang Riedel27 Jul
      • Re: /etc/my.cnfWolfgang Riedel27 Jul
RE: Errcode: 27J S26 Jul
RE: how to set timeout processVictor Pendleton27 Jul
  • Re: how to set timeout processMojtaba Faridzad27 Jul
  • A possible bugLeonardo Javier Belén27 Jul
RE: Errcode: 27J S27 Jul
Re: A possible bugLeonardo Javier Belén28 Jul