in statement - 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)

in statement

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2010-02-24 : 05:24:15

Hi i have a comma deliminated string held in a table, i need to do the following

**i know this works but i need to now make the in dynamic**
select *
from content
where content_id in (12121212,1212232323)

**i cant get this to work**
select *
from content c
INNER JOIN content_meta_tbl cm ON cm.content_id = c.content_id
where content_id in (
select replace(cm.meta_value, ';', ',') as temp
from content c
where content_id = @id
)

**in my test cm.meta_value = 12121212,1212232323**

any ideas how i can getthis to work?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 05:30:08

select c.*
from content c
INNER JOIN content_meta_tbl cm ON cm.content_id = c.content_id
where ','+replace(cm.meta_value, ';', ',')+',' like '%,'+cast(content_id as varchar(10))+',%'
and content_id = @id

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-02-24 : 05:31:08

see this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033#315323

PBUH

craigmacca
Posting Yak Master

142 Posts

Posted - 2010-02-24 : 05:53:38

i have tried that but i more going on in the main select so i think it need to be a in??

this is my full statement..

select adt.mimeType, av.Alias as QuickLink, c.content_title
from content c
INNER JOIN content_meta_tbl cm ON cm.content_id = c.content_id
INNER JOIN AssetDataTable adt ON adt.id = c.asset_id
INNER JOIN taxonomy_item_tbl ti ON ti.taxonomy_item_id = c.content_id
INNER JOIN taxonomy_tbl t ON t.taxonomy_id = ti.taxonomy_id
INNER JOIN UrlAliasTaxonomyView av on av.content_id = c.content_id

where cm.meta_type_id = 115 and c.content_language = @language_id

--do not change these
and t.taxonomy_language_id = 2057
and ti.taxonomy_item_language = 2057
and av.content_language = 2057

and c.content_id in
--(2147485158,2147485149)
(
select replace(cm.meta_value, ';', ',') as test
from content c
INNER JOIN content_meta_tbl cm ON cm.content_id = c.content_id
where cm.meta_type_id = 115 and cm.meta_value <> '' and c.content_id = @content_id
and c.content_language = @language_id
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 09:54:48

quote:
Originally posted by craigmacca

i have tried that but i more going on in the main select so i think it need to be a in??

this is my full statement..

select adt.mimeType, av.Alias as QuickLink, c.content_title
from content c
INNER JOIN content_meta_tbl cm ON cm.content_id = c.content_id
INNER JOIN AssetDataTable adt ON adt.id = c.asset_id
INNER JOIN taxonomy_item_tbl ti ON ti.taxonomy_item_id = c.content_id
INNER JOIN taxonomy_tbl t ON t.taxonomy_id = ti.taxonomy_id
INNER JOIN UrlAliasTaxonomyView av on av.content_id = c.content_id

where cm.meta_type_id = 115 and c.content_language = @language_id

--do not change these
and t.taxonomy_language_id = 2057
and ti.taxonomy_item_language = 2057
and av.content_language = 2057

and c.content_id in
--(2147485158,2147485149)
(
select replace(cm.meta_value, ';', ',') as test
from content c
INNER JOIN content_meta_tbl cm ON cm.content_id = c.content_id
where cm.meta_type_id = 115 and cm.meta_value <> '' and c.content_id = @content_id
and c.content_language = @language_id
)

did you try Madhi's suggestion?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources