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

[rudy]

> say the column holds the values, "1,3,6,9,10,11,12"
> but I want all the records that have "1" (one, not 1 as
> part of 10)
>
> I tried "select * from display where cat contains 1
> order by name" but that returns "1,10,11,12".

hi elfur

your multivalued column is a string

the only satisfactory way to find out if a particular substring is
contained in it is to use an unstring function, and do the test on the
individual pieces

make sense?

cold fusion has the  ListFind(list, value)  function, but this is only any
good *after* the field has been retrieved from the database, but you
probably don't want to retrieve all rows and test them after they come back
to cold fusion (inefficient to say the least)

so you need to do this within the database query

as you have discovered, you can't just do "like" because that returns
"1,10,11,12"

you have a real problem here, don't you


> This cat field is holding ID info, that can be joined with
> another table, but is mainly deciding in which category the
> data is suppose to appear.
>
> While I really think that multi-valued column is the right
> way to go, I'm having some huge problems using it.

all those problems go away if you place the IDs into a separate table

trust me on this

and off the top of my head i just cannot see how you're going to accomplish
the join to the other table



write back if you want more help, i'm here for ya


rudy.ca