[thelist] SQL (or ASP?) selecting from a multi-valued column

[rudy]

> I have a special ID table, that is, I have a table that
> holds only list of categories (id, name)

okay so far

> And I create a table for news items, another one for
> articles, and the third one for member info.
> each of those three tables contain a column where you decide
> where the content is displaying, on which of those 15
> categories the info is showing.

this is okay too, but let's stop right here and ask a few questions

can a news article belong to more than one category?

or is a news article always always always in *at most* one category?

you didn't actually say what your categories are, but i'm going to take a
guess that a news article might be in more than one category

another indication that an item may belong to more than one category is the
problem you originally stated, namely

    > I tried "select * from display where cat contains 1
    > order by name" but that returns "1,10,11,12".

so let's say an item can belong to more than one category

okay, now a category can certainly hold more than one item, yes?

so what you have, in database terminology, is a many-to-many relationship

the way to implement a many-to-many relationship is with two one-to-many
relationships

let's take a closer look

here are three tables

    category (catID, catname)

    newsitem (newsID, newsname)

    newscat (newsID, catID)

let's populate them with some example rows

  insert into category values (1,"politics")
  insert into category values (2,"business")
  insert into category values (3,"entertainment")
  insert into category values (4,"sports")

  insert into newsitem values (844,"isreal votes today")
  insert into newsitem values (845,"bob davis quits lycos")
  insert into newsitem values (846,"sony kills the dreamcast")
  insert into newsitem values (847,"xfl debut a bust")  /*ouch, bad pun*/
  insert into newsitem values (848,"dubya pushes the tax cut")

  insert into newscat (844,1)
  insert into newscat (845,2)
  insert into newscat (846,2)
  insert into newscat (846,3)
  insert into newscat (847,3)
  insert into newscat (847,4)
  insert into newscat (848,1)
  insert into newscat (848,2)

what you should see first is the two one-to-many relationships

each category row can have multiple newscat rows

each newsitem row can have multiple newscat rows

these two one-to-many relationships implement the many-to-many relationship
between news items and categories


take it slow and easy, elfur, and i'm sure it will make sense real soon


you should now be able to see the sql for selecting, say, all the news
items in a particular category

for example, to get all the entertainment news items,

   select newsname
      from newscat, newsitem
      where newscat.catID="3"
         and newscat.newsID=newsitem.newsID

holler if you need more


rudy