Post reply

query an array?

  • September 14, 2005 at 6:37 am

    #165481

    Hi, I'm new here.  Nice website and forum.

    I have a question to ask...hopefully nobody will mind if I do.

    I was trying to write a query in the SQL Server Query Analyzer yesterday.  I want to find every Id that is not a certain set of numbers.

    Like this:

    SELECT * FROM Column WHERE 1=1

    AND ColId <> '1351'

    OR ColId <> '1353'

    OR ColId<> '1387'

    OR ColId <> '1388'

    OR ColId <> '1389'

    OR ColId <> '1390'

    OR ColId <> '1391'

    OR ColId <> '1625'

    OR ColId <> '1626'

    OR ColId <> '1628'

    OR ColId <> '1629'

    OR ColId <> '1630'

    OR ColId <> '1633'

    OR ColId <> '961'

    OR ColId <> '962'

    ORDER BY ColId

    but this seems rather long.  Is there a way to setup some sort of array to store these numbers?

    Thank you

  • September 14, 2005 at 6:42 am

    #589666

    Select columns, list from dbo.YourTable where id not in (1351, 1353, 1387.......)

    Also do you think you could convert this to be used in a stored proc as there are many pitfalls to dynamic sql :

    The Curse and Blessings of Dynamic SQL

  • September 14, 2005 at 6:42 am

    #589665

    SELECT * FROM Column WHERE 1=1

    AND ColId not in ('1351','1353','1387','1388','1389','1390','1391','1625','1626','1628','1629','1630','1633','961','962')

  • September 14, 2005 at 9:27 am

    #589757

     

    Thank you thank you thank you.  I prefer using the solution Justin provided because if I ever needed to add another ID number, I'd just need to modify that query.

     

    Thank you

     

     

  • September 14, 2005 at 9:33 am

    #589762

    No offense, but we posted the same solution???

  • September 15, 2005 at 11:55 am

    #590280

    The choice of 'query modification' over putting the 'not in' the values in a table for a join speaks tremendous amounts. Have we forgotten that an RDMS (erlational database management system) is set based ?

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • September 15, 2005 at 4:57 pm

    #590390

    I have not forgotten What the R in RDBMS means,

    Sometimes its hard to see the forest cuz of all these damn trees.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply