Urgent : call a column in where clause which looks like this 'KS','NE','CO','OK','AR','MO','IA'

Discussion in 'microsoft.public.sqlserver.dts' started by Radhika Shivaraman, Nov 18, 2010.

  1. Hi All :
    I have a tableB with a column SurroundingStates.
    A value in that table is like above.

    Now i join the table and call this column like this

    Select *
    From tableA A
    inner join tableB b
    on A.state in (B.SurroundingStates)
    and A.State = B.State
    where A.State = 'KS'

    what i want the query to look like is :
    Select *
    From tableA A
    inner join tableB b
    on A.state in ('KS','NE','CO','OK','AR','MO','IA')
    and A.State = B.State
    where A.State = 'KS'

    How do i write it , as the first query is not working.
    thank you
     
    Radhika Shivaraman, Nov 18, 2010
    #1
    1. Advertisements

  2. Radhika Shivaraman

    michald Guest

    Select *
    From tableA A
    inner join tableB b
    on (
    (A.state in B.SurroundingStates )
    OR
    (A.State = B.State )
    )
    where A.State = 'KS'
     
    michald, Nov 19, 2010
    #2
    1. Advertisements

  3. Radhika Shivaraman

    Cleary Guest

    Try this
    Select *
    From tableA A
    inner join tableB b
    on (B.SurroundingStates LIKE '%'''+A.state '''%' -- notice the
    embedded quoted quotes
    OR A.State = B.State)
    where A.State = 'KS'
     
    Cleary, Nov 19, 2010
    #3
  4. Radhika Shivaraman

    --CELKO-- Guest

    "A problem well stated is a problem half solved." -- Charles F.
    Kettering

    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, data types, etc. in
    your schema are. If you know how, follow ISO-11179 data element naming
    conventions and formatting rules. Temporal data should use ISO-8601
    formats. Code should be in Standard SQL as much as possible and not
    local dialect.

    Sample data is also a good idea, along with clear specifications. It
    is very hard to debug code when you do not let us see it. If you want
    to learn how to ask a question on a Newsgroup, look at:
    http://www.catb.org/~esr/faqs/smart-questions.html
     
    --CELKO--, Nov 19, 2010
    #4
  5. Looks like your database is incorrectly designed. You would be better of
    having one column per surrounding state rather than a comma-separate list.

    If you insist on that design, see
    http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists for a solution.


    --
    Erland Sommarskog, SQL Server MVP,

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
     
    Erland Sommarskog, Nov 21, 2010
    #5
  6. Radhika Shivaraman

    LP Guest

    Looks like the data model should be refactored - the list of
    surrounding states should be in a table with many to many
    relationship. If Oklahoma is surrounded by Ark, Missouri, and
    Nebraska, it would look like so:

    state related_state
    ---------------------------
    OK AR
    OK MO
    OK NE

    Then the query is a very simple set operation. If you absolutely
    cannot change the model, at least you can use the info here:
    http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists
    to convert the comma-separated list of related states into a temporary
    table, and use that temporary table for your query.
     
    LP, Nov 25, 2010
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.