using 'in' with values from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

using 'in' with values from a table

Author

Topic

happyslug
Starting Member

5 Posts

Posted - 2011-12-10 : 11:16:10

this works - returning all rows where item is either 220001 or 220002

SELECT *
FROM salestable
WHERE Item IN ('220001', '220002')

I want to get the values of the 'in' from a table - something like

SELECT ControlValue FROM SystemControl
WHERE ControlKey = 'SALES'

If ControlValue has a single value e.g. 220001 or 220002 then the following works

SELECT *
FROM salestable
WHERE Item IN (SELECT ControlValue FROM SystemControl
WHERE ControlKey = 'SALES')

but this does not work if I have multiple values in ControlValue.

I have tried

220001,220002

and
'220001','220002'

it returns nothing

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 11:37:34

[code]
SELECT st.*
FROM salestable st
inner join SystemControl sc
ON sc.ControlKey = 'SALES'
AND ',' + sc.ControlValue + ',' LIKE '%,'st.Item + ',%'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

happyslug
Starting Member

5 Posts

Posted - 2011-12-10 : 11:37:53

I might have solved my own problem

SELECT *
FROM salestable
WHERE exists
(
SELECT ControlValue FROM SystemControl
WHERE ControlKey = 'SALESETHANOL'
and ControlValue like '%'+RTRIM(Item)+'%'
)

Any other solutions will be welcome

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-12-12 : 09:53:57

I suggest you to use or operator as

SELECT *
FROM salestable
WHERE Item ='220001' or item= '220002'

Is it work with you?

Kristen
Test

22859 Posts

Posted - 2011-12-12 : 10:15:40

quote:
Originally posted by happyslug

I might have solved my own problem

SELECT *
FROM salestable
WHERE exists
(
SELECT ControlValue FROM SystemControl
WHERE ControlKey = 'SALESETHANOL'
and ControlValue like '%'+RTRIM(Item)+'%'
)

Any other solutions will be welcome

Not good practice to have multiple values in a single column. So your [ControlValue] column in [SystemControl] table should only contain a single value - not a delimited list.

If you need multiple-values for [ControlValue] per row in [SystemControl] table then create a "child" table of [SystemControl] to hold them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources