Help needed in writing SQL - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help needed in writing SQL

Author

Topic

iRobot
Starting Member

2 Posts

Posted - 2009-12-17 : 13:28:37

CREATE table From_Upd(
Col_01 Char(1) Not Null,
Col_02 Varchar(20) null )

INSERT INTO From_Upd (Col_01,Col_02) Values ('1','''01'',''02'',''03'',''04''')
INSERT INTO From_Upd (Col_01,Col_02) Values ('2','''A'',''B'',''C'',''D''')

INSERT INTO From_Upd (Col_01,Col_02, Statement) Values ('3','','Statement for 3')
INSERT INTO From_Upd (Col_01, Col_02,Statement) Values ('4','','Statement for 4')

CREATE table To_Upd(ColA Char(1) Not Null)

INSERT INTO To_Upd (ColA) Values ('A')
INSERT INTO To_Upd (ColA) Values ('B')
INSERT INTO To_Upd (ColA) Values ('C')
INSERT INTO To_Upd (ColA) Values ('D')

--This statement works
SELECT * FROM To_upd
WHERE ColA IN ('A','B','C')

--I need to make this work which should result same as above
SELECT * FROM To_upd
WHERE ColA IN (Select Col_02 FROM From_upd WHERE Col_01 = '2')

Please suggest how to write it?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-17 : 13:37:03

Dynamic SQL...

declare @str varchar(2000)
declare @sql varchar(1000)
select @str = Col_02 FROM From_upd WHERE Col_01 = '2'
select @sql = 'SELECT * FROM To_upd
WHERE ColA IN (' + @str + ')'

exec(@sql)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-17 : 13:53:38

Or...

select a.*
from To_upd a
    inner join From_upd b on b.Col_02 like '%''' + a.ColA + '''%'
where b.Col_01 = '2'

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-18 : 01:13:30

If I think correctly it should be


select a.*
from To_upd a
    inner join From_upd b on b.Col_02 like '%,''' + a.ColA + ''',%'
where b.Col_01 = '2'

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-18 : 06:02:49

quote:
Originally posted by madhivanan

If I think correctly it should be

select a.*
from To_upd a
inner join From_upd b on b.Col_02 like '%,''' + a.ColA + ''',%'
where b.Col_01 = '2'

Madhivanan

Failing to plan is Planning to fail

Um. Really Madhi? I thought the quotes were the delimiter, but if the comma really needs to be factored in too, it needs to be added to both sides...

select a.*
from To_upd a
    inner join From_upd b on ',' + b.Col_02 + ',' like '%,''' + a.ColA + ''',%'
where b.Col_01 = '2'

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-18 : 07:05:32

quote:
Originally posted by RyanRandall

quote:
Originally posted by madhivanan

If I think correctly it should be

select a.*
from To_upd a
inner join From_upd b on b.Col_02 like '%,''' + a.ColA + ''',%'
where b.Col_01 = '2'

Madhivanan

Failing to plan is Planning to fail

Um. Really Madhi? I thought the quotes were the delimiter, but if the comma really needs to be factored in too, it needs to be added to both sides...
select a.*
from To_upd a
inner join From_upd b on ',' + b.Col_02 + ',' like '%,''' + a.ColA + ''',%'
where b.Col_01 = '2'

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Yes it should be added to both sides

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources