Help With Query

I have a table as below
--
name linkcode level
--
brando 1,3,8 1
damroo 1,5 2
rogers 2,7 1
shane 1,7 1
Now I want a query where I pass a level and linkcode (one of the codes
in Comma Seperated values in the record) and it returns me a recordset
with matching criteria like below
I have a variable called lcode (int) and a variable called ulevel (int)
if I pass lcode = 7 and level = 1 it should return rogers, shane in the
record set
My sql is as below
"Select name from tblnames where level =" & ulevel & " and " & lcode &
" in (linkcode)"
I am unable to run this query as it returns an error that
Syntax error converting the varchar value '1,3,8' to a column of data
type int
I even tried by updating the query with a UDF Split function, which
works fine for a specific string CSV as params but does not take the
CSV as a Fieldvalue
"Select name from tblnames where level =" & ulevel & " and " & lcode &
" in (select value from dbo.split(linkcode))"
returns
linkcode is not a recognized OPTIMIZER LOCK HINTS option
can anyone help with this.. or suggest an alternative SQL for my
problem..
I am using MS Sql Server 2000
thanks"Damroo" <damroo@.gmail.com> wrote in message
news:1124719808.592522.132090@.g49g2000cwa.googlegroups.com...
>I have a table as below
> --
> name linkcode level
> --
> brando 1,3,8 1
> damroo 1,5 2
> rogers 2,7 1
> shane 1,7 1
>
Your biggest problem is that you don't have a normalized table. 1NF says
that you each column holds a single value. You need to split your linkcode
table out. Then the queries are quite simple.
You would then have your original table that looks something like:
Table1
--
PrimaryKeycol
name
LinkCodeLookup
Level
LinkCodeLookup
--
ForeignKeyTo_Table1.PrimaryKeycol
LinkCode
New queries would then be a simple join:
SELECT [name] FROM Table1
JOIN LinkCodeLookup ON Table1.PrimarykeyCol =
LinkCodeLookup.ForeignKeyTo_Table1
WHERE Table1.level = ?
AND LinkCodeLookup.LinkCode = ?
Rick Sawtell
MCT, MCSD, MCDBA|||>> I have a table as below
Your table is a mess -- if individual link codes are valid usable attributes
in your business model, then you must represent them as individual values in
your table. Cramming multiple values into a single column is a violation of
the most fundamental principles of relational database design.
The right way ( based on your narrative ) to represent the data would be
like:
name linkcode level
--
brando 1 1
brando 3 1
brando 8 1
damroo 1 2
damroo 5 2
rogers 2 1
rogers 7 1
shane 1 1
shane 7 1
If a redesign is not possible, then you'd have to use one or more of the
workarounds posted frequently in this newsgroup. Some of them are detailed
at:
http://www.sommarskog.se/arrays-in-sql.html
Anith