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

Well, the problems you're encountering might give you an indication
that a "catstr" column isn't the right thing to do...

Create a:
CATEGORIES table
CatID
A
B
C
etc...

YourTHingiesTable:
SomeID
1
2
3

YourThingies_Categories table:
SomeID, CatID:
1, A
1, C
2, D
2, A
2, F

Create a link in YourThingies_Categories for each category something
belongs to. Depending on how you want to display to which categories
something belongs, you need to change your queries, but at least its
very very easy to query for all objects in a specific category.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

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