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.
| Author |
Topic |
|
james_w
Starting Member
21 Posts |
 Posted - 2009-09-03 : 07:37:28
|
| I have a table of users names and email address' also a column [TeamID] that holds csv value such as 1,2,3,4 or 1,3 or could just be a single No such as 3.For example I would want to filter the users to only show users where the [TeamID] has a 3 in it: SELECT [Firstname], [Surname], [Email], [TeamID] FROM [MsUsers] WHERE '3' IN ([TeamID])But it will only return records where [TeamID] = 3, anybody have any ideas why? I've ran IN querys before but never had this problem, seems as if it's because the IN string is from the same table im referencing!Thanks for any help in advance.Regards,James W. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-03 : 07:52:12
|
SELECT [Firstname], [Surname], [Email], [TeamID] FROM [MsUsers] WHERE ','+TeamID+',' like '%,3,%'
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
james_w
Starting Member
21 Posts |
Posted - 2009-09-03 : 07:59:36
|
| Thanks, that seems to work fine.Although still not sure why the IN query wouldn't work when i've done this in a similar way before, do you know why it doesn't work just for future reference?Thanks againJames. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-03 : 08:09:34
|
No - I don't know.But I have tried your statement in sql server 2000 and it is also not working.Sorry.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
james_w
Starting Member
21 Posts |
Posted - 2009-09-03 : 08:18:09
|
| Ok, but thanks for your help anyway as it works your way. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-03 : 08:18:56
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
ra.shinde
Posting Yak Master
103 Posts |
Posted - 2009-09-03 : 10:11:41
|
| Your query was not working because SQL server treats a column as single value and not CSV values. For sql server, '1,2,3' in a column is not equal to '1','2','3'.Rahul Shinde |
|
|
|
|
|
|
|
|
SQL 'IN' QUERY PROBLEMS