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