List:General Discussion« Previous MessageNext Message »
From:Mojtaba Faridzad Date:July 26 2004 3:34pm
Subject:Re: how to deal with a string of categories
View as plain text  
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