the IN clause does not works in my embedded SQL statement

Question

I have following Table

Table User
UserID         Name
1              Om
2              John
3              Kisan
4              Lisa
5              Karel

Table Game
Games          Players
Golf           1,3,5
Football       4

I wrote query:

Select UserId, 
Name from User 
Where UserID IN 
    (Select Players from Game where Games='Golf')

Result:
~~~~~~~
0 Rows

Above query does not return me any result while it works well when i directly specify values for In clause in statement.

Select UserId, Name 
from User 
Where UserID IN (1,3,5)

Result:
~~~~~~~
UserID         Name
1              Om
3              Kisan
5              Karel
3 rows

However when I change the condition in very 1st query with Football:

Select UserId, Name 
from User 
Where UserID IN 
    (Select Players 
     from Game 
     where Games='Football').

This returns me following result:

UserID         Name
4              Lisa
1 row

How I can work around so that my very 1st query returns me the right result?

I think I'm in wrong direction. Help me out!

Just making sure... you have two kinds of capitalization. `UserId` vs `UserID`. Is that the problem? — BlackVegetable, Oct 10 '13 at 17:02
Sorry. That's indeed the Typo error. Thanks for supporting by formatting my question. — user2867958, Oct 10 '13 at 17:31

score 5 · Answer 1 · edited May 23 '17 at 10:31

This is what you get for storing comma separated values in a field. Now you have to split it, using, say this function and do something like

Select User.UserId, User.Name from User 
inner join splitstring((Select Players from Game where Games='Golf')) a
on User.UserID = a.Name

But consider changing your table "Game" design to

Games          Players
Golf           1
Golf           3
Golf           5
Football       4

Then you can do simple

Select User.UserId, User.Name
from User inner join Game
on User.UserID = Game.Players
Where Game.Games = 'Golf'

without any additional functions.

Khan · Answer 2 · 2013-10-10T17:08:48.350

Your first query translates to this:

Select UserId, Name 
from User 
Where UserID IN (`1,3,5`)

Notice that it is a string representation of the IDs, not a comma separated list like in your second query.

There are many Split functions out there written for this very scenario.

You can utilize one of them as such:

DECLARE @PlayersCsv NVARCHAR(MAX)
Select @PlayersCsv = Players from Game where Games='Golf'

Select UserId, 
Name from User 
Where UserID IN 
    (Select Value FROM dbo.Split(@PlayersCsv, ','))

score 0 · Answer 3 · answered Oct 10 '13 at 17:50

DECLARE @xml AS xml
SET @xml = (SELECT cast('<X>'+(''+replace(players,',' ,'</X><X>')+'</X>') AS xml)
            FROM Game WHERE Games='Golf')
SELECT UserId, Name 
FROM User
WHERE UserID IN
(SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N))

SQL Fiddle Results:

| USERID |  NAME |
|--------|-------|
|      1 |    Om |
|      3 | Kisan |
|      5 | Karel |

the IN clause does not works in my embedded SQL statement

3 Answers3