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

[Radhika Shivaraman]

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

[michald]

Post by Radhika Shivaraman
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'
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

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

[Cleary]

Post by Radhika Shivaraman
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'
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

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'

[--CELKO--]

"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

[Erland Sommarskog]

Post by Radhika Shivaraman
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'
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.

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.

[LP]

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.