Split function in ON clause - 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)

Split function in ON clause

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-19 : 02:50:47

Hi,

Here ItemTags.ItemId is having concatenated ItemId like 1,2,3

How can I use this in ON clause.

I used split function to split the ItemTags.ItemId

FROM dbo.Users
INNER JOIN dbo.Items
ON Users.UserId = Items.UserId
INNER JOIN dbo.ItemTags
ON 1 =1 --Items.ItemId =
INNER JOIN (SELECT Item FROM dbo.[Split](',', ItemTags.ItemId)) AS T
ON Items.ItemId = CAST ( T.Item AS INT)
WHERE UserName = 'babu'

I am getting this error

The multi-part identifier "ItemTags.ItemId" could not be bound.

Thanks.

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-19 : 03:29:22

Its not a better idea to have comma seperated values in a column. It violates basic relational db principles. Please post you requirement, table structure and sample data. Someone here will help you out design it better.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 03:34:01

cant use inner join here. use CROSS APPLY instead

FROM dbo.Users
INNER JOIN dbo.Items
ON Users.UserId = Items.UserId
INNER JOIN dbo.ItemTags
ON 1 =1 --Items.ItemId = 
CROSS APPLY (SELECT Item FROM dbo.[Split](',', ItemTags.ItemId)) AS T
WHERE Items.ItemId = CAST ( T.Item AS INT)
AND UserName = 'babu'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources