List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:July 26 2004 3:10pm
Subject:Re: how to deal with a string of categories
View as plain text  
You should never create multi-purpose fields that you will need to use 
to reference other data, which is what you did here. The database can't 
use an index because the "codes" are not in set positions.
You need to create a table for linking the categories  with "mytable", 
since you are trying to create a one to many relation. Your link table 
with have at least two fields, mytable_RecordID and catID. You would 
then do a three table join to get all the data.

If you are only displaying one record at a time, you may just want to 
use your front end (ie. PHP) to "link" the category names.



On Jul 26, 2004, at 10:55 AM, Mojtaba Faridzad wrote:

> 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
>
>
-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

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