[thelist] SQL (or ASP?) selecting from a multi-valued column
rudy
r937 at interlog.com
Mon Feb 5 17:08:37 CST 2001
> 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
More information about the thelist
mailing list